SAS Server Pages: The Next Generation of Lego Pieces for SAS Geeks

Hi, my name is Don. And I am a SAS Geek.

Of course, you probably knew that from the name of my blog :-).

My first introduction to SAS was in February 1975 when, on my first day, of my first job after graduating from college, my boss handed me the SAS manual (yes, singular) and told me to learn how to use it. And in the 38+ years since that day, using SAS and building SAS applications has been at the foundation of just about everything I’ve done professionally.

So while I like the new SAS tools and how much easier it is for folks who aren’t SAS Geeks to do things, as a consultant I still encounter lots of projects where the user looks at what these tools do out of the box and say

     Yea, but, I want it to do this.

          OR

     Yea but, I need it to look like this.

And more times than not it is easier to meet those requirements using what I affectionately call Jurassic SAS (once again, see my blog name). So I’ve spent many years building tools and components that I can piece together (i.e. Lego pieces, or Lincoln Logs for my fellow dinosaurs) to meet the exacting requirements of my clients and users.

I first came up with the idea of what I call SAS Server Pages while writing my first SAS Press book, Building Web Applications with SAS/IntrNet: A Guide to the Application Dispatcher. The basic idea is discussed in this blog posting: A Gentle Introduction to SAS Server Pages.

And now thanks to Rick Langston of SAS R&D, the kinds of applications, user interfaces, reports and more that you can build with the SAS Server Page Lego pieces is leaps and bounds beyond what I originally described. And this week I am happy to say that my e-Book, SAS Server Pages: Generating Dynamic Content, is now available and provides insights and examples on what you, a SAS programmer, can do with PROC STREAM and SAS Server Pages.

The Portal Reporting Framework, an application framework built on the foundation of SAS Server Pages and PROC STREAM is a sample of what can be done with these Lego pieces. Recognizing that a picture is worth a thousand words, and a video is worth a thousand pictures, as discussed in The SAS Bookshelf Post - SAS Server Pages: Generating Dynamic Content, the e-Book only format of this book includes a number of embedded videos to show how to use these tools. For example, this is a short video demo of the Portal Reporting Framework included in Chapter 2 (the video can also be found on my Author Page).

When you look at the short video demo, regardless of whether you mostly use the BI/EBI tools or like to roll your own code consider the paradigm shift offered by PROC STREAM and SAS Server Pages for how to build applications. The video provides a short introduction to just a couple of things you can do with PROC STREAM and SAS Server Pages. I’d like you keep the following points in mind as you review the video demo:

• The Reports TabAs a developer I have complete control over the User Interface. I can lay things out exactly the way the user wants them. Anything that I can do with HTML, JavaScript, etc. is an option. SAS Server pages are simply that HTML, JavaScript, etc. with embedded SAS macro variables, macros and programs. The example shown here is just one of any number of ways that a user could select a report.
• The Common Filters TabAgain I have complete control over the layout of the User Interface – in this case for certain parameters I can de-couple them from the actual report. The example shown here allows for the capture and saving of default parameters that can be used or customized in any report. And just like the same code is used for different selections (e.g., Regions vs. Products), re-use is maximized since the same Lego pieces (i.e., SAS Server Pages) can also be linked to any given report.
• The PFM TabPFM stands for Parameter File Maintenance and is a complete subsystem built in large part on the foundation of SAS Server Pages. Building data-driven or parameter driven applications is a common design approach. Thanks to SAS Server Pages and PROC STREAM, building a web based User Interface to maintain the parameter files that define or drive the application is more easily accomplished.
• The Drill-Down TabSAS Server Pages can be used for more than just User Interfaces. The report, a drillable table, is created with a parameterized SAS Server Page. We tell it the data set, the drill hierarchy and the columns, and it creates the report. A key point here is not only can we generate a report with SAS Server pages; we can create re-usable parameterized templates that perform common functions.

And, of course, there are lots more examples in the book that leverage the BI/EBI toolset (via Stored Processes) as well as examples that only need Base SAS Software.

I hope you consider getting the e- Book. And, I do plan to continue to use my blog to discuss what can be done with PROC STREAM and SAS Server Pages. I hope you will follow my blog and give PROC STREAM and SAS Server Pages a try!

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.