An easy solution for Multi-Sheet EXCEL reporting

shiyiming

From Dapangmao's blog on sas-analysis

<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/-IeTyRxpdQwk/ToR9d08ZjcI/AAAAAAAAAwY/q96uXzs1c3o/s1600/pict1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="http://3.bp.blogspot.com/-IeTyRxpdQwk/ToR9d08ZjcI/AAAAAAAAAwY/q96uXzs1c3o/s400/pict1.jpg" width="400" /></a></div>Currently the only way to output SAS datasets as a multi-sheet EXCEL workbook for reporting is to use <a href="http://support.sas.com/resources/papers/proceedings11/170-2011.pdf">ExcelXP ODS tagset</a>. I like this method a lot, because it can generate stylish multiple EXCEL sheets and is highly customizable. However, in practice it has some weaknesses.  1 - Running this tagset is resource-costly, since it is depends on an 8k lines SAS codes - ExcelXP.sas. While dealing with a large SAS dataset, it always gets jammed. 2- It only allows one grouping variable by the BY statement inside the output procedures (PROC REPORT, PROC PRINT, etc.).  3 - The user often has to estimate the width for each column in EXCEL. <br />
<br />
Actually we can use SAS macro and VBA macro together to obtain high-quality multi-sheet EXCEL workbook. The workflow is pretty simple: first a SAS macro splits a SAS dataset into many XLS files in a folder through ODS HTML targset. Second a VBA macro merges those single XLS files as sheets in to a workbook. For example, SAS shipped with a sample dataset SASHELP.PRDSAL2 with 23040 observations and 11 variables. If we want to generate a multi-sheet EXCEL workbook grouped by two variables such as ‘state’ and ‘year’, we can set up an empty directory in the hard disk and run a macro like below. As a result, we will have a number of small XLS files. <br />
<pre style="background-color: #ebebeb; border: 1px dashed rgb(153, 153, 153); color: #000001; font-size: 14px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
%macro split(data = , folder = , clsvar1 = , clsvar2 = );
   options nocenter nodate nonumber ps = 9000;
   title; footnote;
   ods listing close;
   proc sql noprint;
      create table _tmp01 as
      select &amp;clsvar1, &amp;clsvar2, count(*) as number
      from &amp;data
      group by &amp;clsvar1, &amp;clsvar2
      order by &amp;clsvar1, &amp;clsvar2
   ;quit;
   data _tmp02;
      set _tmp01 nobs = nobs;
      where number gt 0;
      index = _n_;
      call symput('nobs', nobs);
   run;
   %do i = 1 %to &amp;nobs;
      proc sql noprint;
         select &amp;clsvar1, &amp;clsvar2
            into:clsvar1name,:clsvar2name
         from _tmp02
         where index = &amp;i
      ;quit;
      %let filepath = &amp;folder\%sysfunc(dequote(&amp;clsvar1name))_%sysfunc(dequote(&amp;clsvar2name)).xls;
      ods html file = "&amp;filepath " style = minimal;
      proc print data = &amp;data noobs label;
         where &amp;clsvar1 = "&amp;clsvar1name" and &amp;clsvar2 = &amp;clsvar2name;
      run;
   %end;
   ods listing;
   ods html close;
%mend;
%split(data = sashelp.PRDSAL2, folder = C:\test1, clsvar1 = state , clsvar2 = year)
</code></pre><div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/-doG_sAoJhxU/ToR9uJRxbMI/AAAAAAAAAwg/9WY1AAHhJuo/s1600/2011-09-29_085033.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="364" src="http://4.bp.blogspot.com/-doG_sAoJhxU/ToR9uJRxbMI/AAAAAAAAAwg/9WY1AAHhJuo/s400/2011-09-29_085033.jpg" width="400" /></a></div>Then we can open EXCEL, press ALT+F11, paste the VBA code below and run it. Then we will be able to have a decent multi-sheet EXCEL workbook. The biggest strength for this method is that it is very fast – the overall process (running SAS macro and VBA macro) only takes less than a minute for this relatively large dataset SASHELP.PRDSAL2. And it can be expanded to many grouping variables by modifying the SAS macro a little. In conclusion, for big data EXCEL reporting, combining SAS macro and VBA macro together is a good alternative other than ExcelXP ODS tagset. <br />
<pre style="background-color: #ebebeb; border: 1px dashed rgb(153, 153, 153); color: #000001; font-size: 14px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
Sub Merge()
   Dim wbDst As Workbook
   Dim wbSrc As Workbook
   Dim wsSrc As Worksheet
   Dim MyPath As String
   Dim strFilename As String
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\test1" ' &lt;-----change path
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath &amp; "\*.xls", vbNormal)
    If Len(strFilename) = 0 Then Exit Sub
    Do Until strFilename = ""
        Set wbSrc = Workbooks.Open(Filename:=MyPath &amp; "\" &amp; strFilename)
        Set wsSrc = wbSrc.Worksheets(1)
        wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
        wbSrc.Close False
        strFilename = Dir()
    Loop
    wbDst.Worksheets(1).Delete
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
</code></pre><div class="blogger-post-footer"><img width='1' height='1' src='https://blogger.googleusercontent.com/tracker/3256159328630041416-6547890132825647370?l=www.sasanalysis.com' alt='' /></div><img src="http://feeds.feedburner.com/~r/SasAnalysis/~4/3pv4XZnxPqA" height="1" width="1"/>