SAS makes spreadsheet for reporting

Excel is the last stop in the pipeline of my daily work. Most clients of mine like colorful multi-sheet spreadsheets more than plain CSV files (I guess they all use Windows). I am not a power user of Excel, and honestly I am a little afraid of it: sometimes it got wrong while I accidently dragged the cells to make duplicates. As the result, I tend to have everything ready in SAS and treat Excel as the close box. At the beginning I preferred to use the high-tech ODBC engine to exchange data; eventually I gave up when I found that my IT support doesn’t know how to add SAS/ACCESS module though we have the license. The EXPORT procedure is widely used, but it does not allow traffic lighting, which labels value with distinctive colors. MSOFFICE2K, an ODS tagset from SAS to Excel, ceased to work [Ref. 1]. Besides those methods, SAS has some alternative ways to prepare pretty Excel reports. Each year Vincent in SAS updates ExeclXP, a 6k-line SAS code, for multi-sheeting output by ODS tagset [Ref. 2]. This year in his paper, Romain elaborated possibly all the 8 methods, and no wonder his amazing summarization won the best paper award in the code’s corner section [Ref. 3].

Three routines in SAS satisfy 99% of my requirements for an Excel report. First, SAS‘ HTML ODS tagset can directly write XLS file. It is good enough for any single-sheet spreadsheet. Second, thanks to Vincent, ExcelXP provides the perfect tool to generate multi-sheet spreadsheets. The only place where I need to do extensive coding is to find optimal values for the ‘absolute_column_width’ setting in Excel. Hope next year the code would be improved to address this particular question. Third, if part of the cells in Excel needs calculation, I would like to call DDE, a old but still robust technology, to read data into SAS and later write back (I also use R as a scientific calculator to do algebra).

In conclusion, the report by codes can be reproduced more easily than that by many point-and-click operations. That’s probably why I like SAS more than Excel.

Reference:
1. ‘Vanilla output using ODS’. SAS-L. 05May2011
2. Vincent DelGobbo. ‘Creating Stylish Multi-Sheet Microsoft Excel Workbooks the Easy Way with SAS’. SAS Global 2011.
3. Romain Miralles. ‘Creating an Excel report: A comparison of the different techniques’. SAS Global 2011. http://support.sas.com/rnd/papers/index.html

/*******************READ ME*********************************************
* -  SAS MAKES SPREADSHEET FOR REPORTING -
*
* SAS VERSION:  SAS 9.1.3
* DATE:         11may2011
* AUTHOR:       hchao8@gmail.com
*
****************END OF READ ME******************************************/

****************(1) MULTIPLE SHEET REPORTING FOR EXCEL *****************;
******(1.1) FIND EXCEL'S COLUMN WIDTH AND MAKE TRAFFIC LIGHTS***********;
%macro excelcol(data = , groupvar = );
   /*****************************************************************
   *  MACRO:      excelcol()
   *  GOAL:       find the optimum column size for EXCEL  
   *  PARAMETERS: data       =  data used for output
   *              groupvar   =  variable for sheet separation
   *****************************************************************/
   %global var_list len_list;
   ods listing close;
   ods output variables = _varlist;
   proc contents data = &data; 
   run;
   proc sort data = _varlist;
      by format num;
   run;
   data _varlist;
      set _varlist;
      len_var = length(variable);
      len_lab = length(label);
      max_len = max(of len:);
   run;
   
   proc sql;
      select variable into: var_list separated by ' '
      from _varlist
      where lowcase(variable) ne "&groupvar"
      ;
      select max_len into: len_list separated by ','
      from _varlist
      where lowcase(variable) ne "&groupvar"
      ;
   quit;
   ods listing;
%mend excelcol;
%excelcol(data = sashelp.cars, groupvar = origin);

proc format;
   value price  
      40000 - high = '#FFFFCC'
      26000 -< 40000 = 'Yellow'
      other = '#FF9900';
run;
ods listing;

******(1.2) WRITE FINAL REPORT TO EXCEL********************************;
proc template;
   define style styles.xlsansprinter;
      parent = styles.sansprinter;
      style header from header /
         font_size = 10pt just = center vjust = bottom;
   end;
run; quit;

ods tagsets.excelxp path="c:\" file="cars.xml" style=xlsansprinter
               options(sheet_interval="bygroup" sheet_label=" "
                            suppress_bylines="yes" autofilter="2-14"
                            absolute_column_width="&len_list");
title; footnote;
proc report data = sashelp.cars nowd split='*';
   by make;
   column &var_list;
   define model / style(column) = [just=center font_weight=bold];
   define invoice / style(column) = [foreground=lime];
   compute model;
      rownum + 1;
      if (mod(rownum, 2) ne 0)
      then call define(_row_, 'style', 'style = [background=#99ccff]');
   endcomp;
   compute msrp;
      call define(_col_, 'style', 'style = [background=price.]'); 
   endcomp;
   compute before _page_;
      line "Made by &sysuserid on &sysday., &sysdate";
   endcomp;
run; quit;
ods tagsets.excelxp close;
ods listing;

****************(2) SINGLE SHEET REPORTING FOR EXCEL *****************;
ods listing close;
ods html file = "c:\cars2.xls" gpath = "c:\" style = minimal;
title; footnote;
proc print data = sashelp.cars; 
   id make; 
   var &var_list;
   var msrp / style = [background=price.]; 
run;

proc gplot data = sashelp.cars;
   plot msrp * invoice;
run;
ods html close;
ods listing;

****************(3) DDE BETWEEN SAS AND EXCEL ************************;
******(3.1) EXTRACT DATA FROM EXCEL TO COMPUTE IN SAS*****************;
filename _infile dde 'clipboard';
data _tmp01;
   infile _infile notab missover dlm = '09'x dsd;
   informat var1 var2 dollar8.;
   input var1 var2;
run;

data _tmp02;
   set _tmp01;
   mean = mean(of var:);
   std = std(of var:);
run;

******(3.2) WRITE STATISTICS BACK TO EXCEL****************************;
options noxwait noxsync;
x "c:\cars2.xls";

filename _outfile dde 'excel|c:\[cars2.xls]cars2!r2c17:r28c18';
data _null_; 
   set _tmp02;
   file _outfile notab dlm='09'x; 
   format mean std dollar8.; 
   put mean std; 
run; 

****************END OF ALL CODING***************************************;