Wednesday, February 27, 2013

A SAS Server Page Approach to inserting SAS Data Tables into RTF Documents

There are a number of ways to insert tables of SAS data into Word documents. ODS can be used to create the table and then it can be simply cut and pasted into the document. Alternatively the SAS Add-In for Microsoft Office can be used. And I am sure there are lots of other ways.

What I would like to illustrate with this blog posting is another way to do this using PROC STREAM and SAS Server Pages. The Use Case started with a comment that wouldn't it be nice if, for example, someone writing up a summary of clinical trial study could:
  • compose the document with all the appropriate commentary
  • enter some form of markup or commands for where the tables of data are to be inserted
  • hand that off to a SAS programmer or analyst to insert all the needed tables.
They key point was to make it easy to insert lots of tables and to refresh them easily once/if the data changed.

So that got me thinking that it would be nice if the markup could simply be a call to a SAS macro that generates the table. Something like the RTF file shown in Figure 1.

Figure 1. Sample RTF SAS Server Page.
The idea being that the RTF file is used as input to PROC STREAM (i.e., it is a SAS Server Page) creating the output RTF file shown in Figure 2 (shown in part).

Figure 2. Output document with embedded tables of SAS data.
The following sample program illustrates using PROC STREAM to do this.
filename inssp "&root\SSPs\Insert SAS Tables.rtf"
         lrecl = 32755;
filename out "&root\SSPs\With Embedded SAS Tables.rtf"
         lrecl = 32755;
filename newline temp lrecl = 32755;
options insert = (sasautos=("&root\macros"));
data _null_;
 /* make sure line feeds in the input
    document are preserved */
 infile inssp;
 input;
 file newline;
 if _n_ = 1 then put _infile_;
 else put '&streamDelim newline;' _infile_ ;
run;
proc stream outfile = out quoting = both;
BEGIN &streamDelim; %include newline;
;;;;
run;

The generateRTFTable Macro

OK. Now is the time for my disclaimer. This macro is very much a work-in-progress/proof-of-concept. For now, my goal was to just verify that it was possible to write a macro that could generate RTF text to embed a table of SAS data.

Frankly I am not sure that the time needed to do the research on what options might be needed and how to parameterize the RTF text is worthwhile. Perhaps readers of this blog entry can provide their thoughts.

Considerations when creating RTF files as SAS Server Pages

In chapter 4 of my upcoming e-book, SAS® Server Pages: Generating Dynamic Content, I describe some special considerations to keep in mind when using Microsoft Word to create RTF SAS Server Pages. And since I expected to discover more issues, I created an article Creating RTF SAS Server Page at www.sasCommunity.org to act as a repository of such additional issues.

For the purposes of this posting, the relevant concern (described in the book) is the Revision Save ID (RSID) feature of Microsoft Word. It can cause problems when text that is part of a SAS macro language element is added or deleted, or when the properties of that text are changed. The changes are tagged with a Save ID, which is a random number that changes each time the document is saved. These Revision Save IDs are primarily used when merging or comparing two documents with a common history but no revision marks (Track Changes is not turned on). The inserted RSID markup text causes PROC STREAM to not recognize the text as a macro variable reference, macro function call, or macro invocation. One way to avoid such errors is to disable the RSID feature. In Word 2003, deselect Store random number to improve accuracy under Tools ► Options ► Security Tab. In Word 2010, deselect Store random numbers to improve Combine accuracy under File ► Options ► Trust Center ► Trust Center Settings.) This is a global change and will apply to any future Word documents you edit (unless you change the setting back).

Next Steps

For anyone interested in trying this out, this zip file can be downloaded from www.sasCommunity.org. It contains the input RTF file, along with the above program and the generateRTFTable macro. And while not needed to try this yourself, I also included the output RTF file shown above. Just extract the zip file to a folder and add a %let statement to the above code and assign that location to the macro variable root.

And please consider this an open-call for anyone interested in collaborating on an effort to parameterize and generalize the generateRTFTable macro.