|Figure 1. Sample Excel Report|
The ExcelXP tagset (which can create XML spreadsheets) does not provide the level of control needed to produce this report. And the soon to be deprecated (if not already), DDE approach is only an option on Windows. However, such a report, with exacting output requirements, is a good candidate for PROC STREAM, since PROC STREAM can process text files.
In the book I describe an approach with multiple steps:
- Save the current Excel report as an XML spreadsheet - with just one Worksheet.
- Open the XML file in a text editor and split it into separate XML files:
- One for the header
- One for the worksheet
- One for the trailer section
- Replace the data values with macro variable references
- Write a macro that loops thru the regions, creates macro variables for each product and then calls PROC STREAM to create a cumulative output XML file.
Figure 2 is a snippet of the XML that creates the worksheets.
|Figure 2. XML Worksheet Body with embedded macro call|
- the title for the report.
- the solid green line
- the macro generateXMLRows (included below)
- the solid green line
- the total line (using Excel formulas)
|Figure 3. Snippet of the macro to geenrate the XML data rows.|
/* PROC STREAM has been packaged as a macro */
/* using the sashelp.shoes data since it is available
with the SAS install */
/* Region will be the Worksheet name - so we need to
remove the / which is not valid in Excel */
Region = translate(Region,'-','/');
/* create an extra output row for an overall total */
Region = 'Global Operations';
proc summary data = shoes nway;
/* create the data set with the summary level
needed for the report */
class region product;
output out = sums(drop=_freq_ _type_) sum = ;
proc sql noprint;
/* create an "array" of macro variables for the
values of the variable Region */
select distinct region
/* create the macro variables referenced in the
macro call */
%let data = sums;
%let rowStub = Product;
%let var = Sales;
%do i = 1 %to &sqlobs;
%let Region = &&Region&i;
%let where = strip(Region) = "&Region";
So, for readers of SAS® Server Pages: Generating Dynamic Content, this posting is the promised example that uses a macro to generate the rows instead of using macro variables for a fixed set of products.