As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Creating Summary and Detail Sections in an Excel Worksheet Using the ExcelXP Tagset

From sasCommunity
Jump to: navigation, search


I often want to create an Excel report that shows totals at the top and detail lines at the bottom. Using the standard data set SASHELP.Class, my worksheet might look like this:

SummaryDetailExcel-01-want.png

If I select a value in one of the filters, I want the filtered subtotal at the top to show the total of only the filtered records. For example, if I filtered on Age=11, the worksheet would display like this:

SummaryDetailExcel-02-filtered.png

The Unfiltered totals remain the same, but the Filtered totals (and the calculated ratio) now reflect only the data for 11 year-olds.

The easiest way to accomplish all this is just to write two tables on the same worksheet - the first containing the summary lines, and the second containing the detail lines.

To produce this report using the ExcelXP tagset, we first have to see how to produce it in Excel itself.

In Excel, go into Tools / Options / View and select Formulas under Window options highlight in a red box below:

SummaryDetailExcel-03-options.png

After you click OK, you'll see the formulas needed to calculate the numbers:

SummaryDetailExcel-04-formulas.png

To create a worksheet that looks like this one, you'll have to get those formulas into a worksheet via the ExcelXP tagset.

Besides a basic familiarity with the ExcelXP tagset (or a willingness to just follow a recipe), you need to know two things:

- To insert a formula into a cell, just write a value starting with an equals sign.

- The ExcelXP tagset uses R1C1 notation rather than A1 notation.

So first, let's convert to the notation that the tagset needs by going into Tools / Options / General and checking R1C1 reference style:

SummaryDetailExcel-05-r1c1.png

The formulas now appear in the R1C1 format:

SummaryDetailExcel-06-r1c1.png

R8C4 refers to the cell in the 8th row and 4th column - it would be called cell D8 in the standard A1 reference style. Brackets indicate a relative location, so RC[-2] refers to the cell two columns to the left in the same row.

This notation takes some getting used to, but it turns out to be convenient for SAS - if you want to refer to the column three to the right, for example, you can just write RC[3] instead of having to know find the current column number, add three to it, and then convert that new column number to a column letter.

What about the filtering itself? That's done with the autofilter option to the ExcelXP tagset. You can't see it here, but the worksheet it set up so the headers won't scroll out of view if you scroll down in the data; that's also done with a tagset option. And finally, you need to tell the tagset that the filter applies to the second table, not the first one, and that the two tables should go on the same worksheet; again this is done with tagset options.

Here's the SAS code:

 data counts (keep=title heightsum weightsum ratio);
 
    length title heightsum weightsum ratio $32.;
 
    title = 'Unfiltered';
    heightsum = cat('=sum(r8c4:r', put(nobs+7, z5.), 'c4)'); 
    weightsum = cat('=sum(r8c5:r', put(nobs+7, z5.), 'c5)'); 
    ratio = '=r[0]c[-2]/r[0]c[-1]';
    output;
 
    title = 'Filtered';
    heightsum = cat('=subtotal(9,r8c4:r', put(nobs+7, z5.), 'c4)'); 
    weightsum = cat('=subtotal(9,r8c5:r', put(nobs+7, z5.), 'c5)'); 
    output;
 
    stop;
 
    set sashelp.class nobs=nobs;
 
 run;
 
 ods tagsets.excelxp file='c:\temp\filters.xml' style=sasweb
    options(
       sheet_interval='none'
       autofilter_table='2'
       embedded_titles='yes'
       autofilter='all'
       frozen_headers='7'
       row_repeat='7'
       );
 
 title 'Summary + Detail';
 
 proc report data=counts nowindows missing nocenter;
 
    column title heightsum weightsum ratio;
    define title     / '' style={background=white};
    define Heightsum / 'Height' style={just=right tagattr="format:#,##0.0"};
    define Weightsum / 'Weight' style={just=right tagattr="format:#,##0.0"};
    define ratio     / 'Ratio'  style={just=right tagattr="format:#,##0.00"};
 
 run;
 
 title;
 
 proc print data=sashelp.class noobs
       style(header)={just=left};
    var name sex age;
    var height weight / style={tagattr="format:#,##0.0"};
 run;
 
 
 ods tagsets.excelxp close;