Search code examples
sqlsassas-macro

How to print groupped data in SAS webout file

I created a data set as below

Custom_Data_Set
Branch | Code | Region | Col_1 | Col_2 | Fee_1 | Fee_2 | Total
 1st      01     East     val     val     150     250     400
 1st      01     East     val     val     100     120     220
 1st      01     East     val     val     130     120     250
 2nd      02     West     val     val     200     250     450
 2nd      02     West     val     val     300     400     700

No I want to print this data to my web file in groups like

Branch: 1st  Code: 01  Region: East
  Col_1 | Col_2 | Fee_1 | Fee_2 | Total
   val     val    150     250     400
   val     val    100     120     220
   val     val    130     120     250

   Total          380     490     870
Branch: 2nd  Code: 02  Region: West
  Col_1 | Col_2 | Fee_1 | Fee_2 | Total
   val     val    200     250     450
   val     val    300     400     700

   Total          500     650    1150

 GrandTotal       880     1140   2020

I can do simple print using 

data _null_;
put '<html><body><table>';
run;

data _null_;
 put numberOfObservations=;
 set work.Custom_Data_Set nobs=numberOfObservations;
 file _webout; 
 put '<tr>
         <td>'; put Branch ; put'</td>
         <td>'; put Code ; put'</td>
         <td>'; put Col_1 ; put'</td>
         <td>'; put Col_2 ; put'</td>
         <td>'; put Fee_1 ; put'</td>
         <td>'; put Fee_2 ; put'</td>
         <td>'; put Total; put'</td>
         </tr>';
run;

data _null_;
put '<tr>
         <td>Total</td>
         <td></td>
         <td></td>
         <td></td>
         <td>'; put &Fee_1_total ; put'</td>
         <td>'; put &Fee_2_total ; put'</td>
         <td>'; put &_total; put'</td>
         </tr>
    </table></body></html>';
run;

But I'm having trouble printing that in grouping with total and grand total and placing only one entry of branch, code an region of whole group.

We can do grouping using branch and code.

PS: Fee_1_total, Fee_2_total and _total are macro variables that I'm getting by proc sql into statement and assigning them as sum(relevent_column).

Solution

  • You can just do the whole thing in one data step. No need for macro variables or PROC SQL summary code. Just make sure the data is sorted by your grouping variables.

    You can use a DOW loop to iterate the data step once for each BY group. Use _N_=1 and the variable created by end= option on the set statement to know when to write beginning and ending. Use first.branch to know when to write the block header row. You can accumulate the sums yourself into new dataset variables and output the total row after the last row in the by group.

    data _null_;
  file _webout ;
  if _n_=1 then do;
    putlog nobs= comma20. ;
    put
  '<html><body><table>'
    ;
  end;
  do until (last.region) ;
    set have nobs=nobs end=eof;
    by branch code region ;
    if first.region then put
  '<tr><th colspan="5">' Branch= code= region= '</th></tr>'
 /'<tr><th>Col_1</th><th>Col_2</th><th>Fee_1</th><th>Fee_2</th><th>Total</th></tr>'
    ;
    put
  '<tr><td>' col_1 '</td>'
      '<td>' col_2 '</td>'
      '<td>' fee_1 '</td>'
      '<td>' fee_2 '</td>'
      '<td>' total '</td></tr>'
    ;
    sum_1 = sum(sum_1,fee_1);
    sum_2 = sum(sum_2,fee_2);
    sum_t = sum(sum_t,total);
  end;
  put
  '<tr><td colspan="2">Total</td>'
      '<td>' sum_1 '</td>'
      '<td>' sum_2 '</td>'
      '<td>' sum_t '</td></tr>'
   ;
   grand_1+sum_1;
   grand_2+sum_2;
   grand_total+sum_t;
   if eof then put
  '<tr><td colspan="2">GrandTotal</td>'
      '<td>' grand_1 '</td>'
      '<td>' grand_2 '</td>'
      '<td>' grand_total '</td></tr>'
/ '</table></body></html>'
   ;
run;

    I copied your posted data into a data step to create something to program with. I changed the values of COL_1 and COL_2 to be distinct.

    Resulting table looks like this when opened in IE.

    enter image description here