Generating a custom XML Spreadsheet

In chapter 7 of my e-book (due to be released on/around April 9, 2013), SAS® Server Pages: Generating Dynamic Content, I describe creating the output shown in Figure 1, with the additional constraint that the users have specified that the generated report must match it exactly (that is, the green borders around the table must be exactly 10 pixels, the black border must separate the total, green lines must separate rows within the green border, and so on). The report has multiple tabs (i.e., Worksheets), one for the overal total (as seen in Figure 1) and then one for each Region.


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:

1. Save the current Excel report as an XML spreadsheet - with just one Worksheet.
2. Open the XML file in a text editor and split it into separate XML files:
1. One for the header
2. One for the worksheet
3. One for the trailer section
3. Replace the data values with macro variable references
4. 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.

One of the reviewers commented that an approach where a macro was used to generate the XML to create the rows instead of macro variables would be a nice addition. Instead of adding yet another example to the book, I decided to take advantage of the e-book format by including links in the book to blog entries (e.g., like this one) that expanded upon the included examples.

Figure 2 is a snippet of the XML that creates the worksheets.

Figure 2. XML Worksheet Body with embedded macro call

The <row> . . . . </row> sections are:

1. the title for the report.
2. the solid green line
3. the macro generateXMLRows (included below)
4. the solid green line
5. the total line (using Excel formulas)

Figure 3 shows the relevant snippets of the generateXMLRows macro that generate the <row> . . . . </row> sections with the data values.


Figure 3. Snippet of the macro to geenrate the XML data rows.

And last, here is the macro that does the processing to create an XML spreadsheet with a tab (aka a Worksheet) for each region.

%macro totalsByRegion; 
 filename _webout
   "&root\output\ProductsAllSpreadsheetMacroLoop.xml";

 /* PROC STREAM has been packaged as a macro */
 %sasServerPage(page=ProductsSpreadsheetHeader.xml)

 data shoes/view=shoes;
  /* using the sashelp.shoes data since it is available
     with the SAS install */
  set sashelp.shoes;
  /* Region will be the Worksheet name - so we need to
     remove the / which is not valid in Excel */
  Region = translate(Region,'-','/');
  output;
  /* create an extra output row for an overall total */
  Region = 'Global Operations';
  output;
 run;

 proc summary data = shoes nway;
  /* create the data set with the summary level
     needed for the report */
  class region product;
  var sales;
  output out = sums(drop=_freq_ _type_) sum = ;
 run;

 proc sql noprint;
  /* create an "array" of macro variables for the
     values of the variable Region */
  select distinct region
  into:region1-:region999
  from sums
  order region;
 quit;

 /* 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";
    %sasServerPage
       (page=ProductsSpreadsheetBodyMacroLoop.xml
       ,mod=mod
       )
 %end;

 %sasServerPage
    (page=ProductsSpreadsheetTrailer.xml
    ,mod=mod
    )

%mend totalsByRegion;

%totalsByRegion

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.