CODEWITHSUNDEEP
sassas-macro
Azeem112
Azeem112

Reputation: 377

How to show sas data set using group by in report

I'm working in SAS EGRC 6.1 module and I've a data set as below in work library

SAGIA_Detail
 BranchCode BranchName RegionCode SagiaLicNo IssuingDate ExpiryDate  
   20         Abc      Central        1          1/1/2000     1/1/2001
   20         Abc      Central        2          1/1/2000     1/1/2001
   10         def      East           3          1/1/2000     1/1/2001

 BranchManager IsIssuance IssuanceFees IsRenewal RenewalFees Total
    name          Yes        100         No         0         100
    name          Yes        100         Yes        100       200
    name          Yes        200         Yes        100       300

I want to print this data set in my SASStoredProcess report with group by of BranchName or by BranchName. I wrote this code but but its only printing the data lines of my data set without any total or grandtotal row of html table.

In short it's not executing any code after the end statement of do loop. Please help me to figure out that where I did wrong in my coding.

data _null_;
 file _webout; 
 put '<html><body><table>';
 do until(last.region);
 set SAGIA_Detail nobs=nobs end=eof;
 by BranchCode RegionCode BranchName;
 if first.BranchCode then put
            '<tr><th colspan="9"><span><b><u>BranchCode</u></b>: ' BranchCode '</span><span><b><u>BranchName</u></b>: '  BranchName '</span><span><b><u>RegionCode</u></b>: ' RegionCode '</sapn></th></tr>
            <tr class=Head>
                        <th>Sagia LicenseNo</th>
                        <th>Issue Date</th>
                        <th>Expiry Date</th>
                        <th>Manager Name</th>
                        <th>Is Issuance</th>
                        <th>Issuance Fees</th>
                        <th>Is Renewal</th>
                        <th>Renewal Fees</th>
                        <th>Total</th>
                    </tr>';
            put '<tr>
                    <td>';put SagiaLicNo; put '</td>
                    <td>';put IssuingDate date10.; put '</td>
                    <td>';put ExpiryDate  date10.; put '</td>
                    <td>';put BranchManager; put '</td>
                    <td>';put IsIssuance; put '</td>
                    <td>';put IssuanceFees comma12.2; put '</td>
                    <td>';put IsRenewal; put '</td>
                    <td>';put RenewalFees comma12.2; put '</td>
                    <td>';put Total comma12.2; put '</td>
                </tr>';

                Total_IssuanceFees = sum(Total_IssuanceFees,IssuanceFees);
                Total_RenewalFees = sum(Total_RenewalFees,RenewalFees);
                Total_TotalFees = sum(Total_TotalFees,Total);
  end;

  put           '<tr>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                </tr>';

put             '<tr class=Grand>
                    <td>Grand</td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td></td>
                    <td>' Total_IssuanceFees  '</td>
                    <td></td>
                    <td>' Total_RenewalFees  '</td>
                    <td>' Total_TotalFees '</td>
                </tr>';

                Issuancegrand+Total_IssuanceFees;
                Renewalgrand+Total_RenewalFees;
                Totalgrand+Total_TotalFees;

 if eof then put
  '<tr class=Grand>
      <td colspan="6">GrandTotal</td>'
      '<td>' Issuancegrand '</td>'
      '<td>' Renewalgrand '</td>'
      '<td>' Totalgrand '</td></tr>'
  '</table>
 </body>
</html>';

run;

Upvotes: 1

Views: 49

Answers (1)

DomPazz
DomPazz

Reputation: 12465

First, you are using a BY clause, make sure your data is sorted.

proc sort data=SAGIA_Detail;
 by BranchCode RegionCode BranchName;
run;

Second, your sample data has the variable BranchRegion but your code uses RegionCode. Are these the same? Make sure your code is using a variable that actually exists.

Further, your data step references last.region. There is no region variable.

Fixing those should get you closer to what you are looking for.

Upvotes: 1

Related Questions