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.

Rich Text Data Entry and HTML5

On occasion I've had requests on projects where the user wanted a way to enter some text and be able to mark the text as bold or italic. I've been able to convince the users that entering some markup is the way to go. They typically go along after some convincing.

The contenteditable attribute, available in HTML5 makes such editing easier. You can try it out yourself below:

1. The text between the lines below is tagged with that attribute
2. Feel free to edit it:
1. Add line feeds (i.e., hit the Enter key)
2. Highlight text and use CTRL B, or I or U
3. The click the button to see the HTML text with your changes.

This text can be edited by the user. Feel free to edit it. You can even hit the enter key for a line feed. You might want to do this at the beginning of every sentence. You can also highlight some text and use CTRL-B, CTRL-I, CTRL-U to make the text bold or italic or underlined.

So how did I do that? All I had to do is wrap the text in a DIV tag with the contenteditable attribute enabled, i.e.,

<div contenteditable="true" id="cmnts">
The text I want the user to be able to edit.
</div>

The DIV tag has an ID attribute so I can get the HMTL text using the innerHTML attribute.

Now your next question, is how can I upload the value to the server since the text is not in a form field (e.g., an INPUT or TEXTAREA tag)? All I need to do is to create a hidden form field in my form, e.g.,

<input type="hidden" name="userText" id="userText">

and then in my form tag I add an onSubmit attribute to assign the HTML text to the form field, e.g.,

onSubmit="document.getElementById('userText').value =
          document.getElementByID('cmnts').innerHTML;"

On the SAS Server side (e.g., the SAS/IntrNet® Application Dispatcher or the Stored Process Server), like for any other form field, a macro variable (userText) contains the HTML text that can be saved or used however the applications sees fit.

This is very much a Work In Progress and as I continue my research and experimentation with HTML5, hopefully I will find an easy to implement facility for a more complete Rich Text Editor. But for now, the contenteditable attribute is a pretty good alternative IMO.

Using DOSUBL to write Macros as functions

In my last post, DOSUB and DOSUBL - Data Driven Development, I mentioned one of the many uses of these very powerful new functions. I'd like to discuss another Use Case: using them to facilitate creating your own macros that work like functions.

Macros that need to return a single value, as opposed to generating code are often written as functions so they can be used, for example, as follows:

• %let total = %getMax(data= . . ., var =  . . . );

or

• retain  denom %getMax(data= . . ., var = . . . );

However if the macro needs to run some SAS code, it is not possible to call the macro this way since the generated code would be returned by the macro instead of the desired value. There are a number of work-arounds that many SAS programmers have used in this case, such as:

• store the value into a macro variable that the calling program must know about;
• save the value into a data set that the calling program must use to get the value;
• use the %sysfunc macro along with the data access functions.

The DOSUB and DOSUBL functions provide another way to do this. They can be used via the %sysfunc macro to run the code. Consider the following real-life Use Case, which I use the Parameter File Maintenance (PFM) subsystem discussed in my upcoming e-book, SAS® Server Pages: Generating Dynamic Content. PFM is a web-based data entry facility that I've implemented on a number of projects to support managing the parameter files that drive the application. The getSurrogateKey macro is used as part of the update process to generate an analog to an auto-number surrogate key (something that many databases support with different names/terminology, but that SAS data files do not).

%macro getSurrogateKey
      (data = /* name of the dataset being updated */
      ,key =  /* name of surrogate key variable */
      );
 %local newkey;
 %if &&&key = . or &&&key = %then
 %do; /* generate a new surrogate key value */
   %let rc = %sysfunc(dosubl(
         'proc sql noprint;
           select cats(sum(max(&key),1))
           into:newkey from &data;quit;'
             ));
   %put NOTE: &key value of &newkey generated.;
   &newkey
 %end; /* generate a new surrogate key value */
 %else &&&key;
%mend getSurrogateKey;

The macro is called unconditionally by the update process and if the macro variable, from the Data Entry page (a SAS Server Page), for the key is missing, a new row is being added and this requires a new key value. The DOSUBL function is used, via %SYSFUNC) to run the PROC SQL code to get the next auto-number key value. Since DOSUBL is used, the macro execution does not return the SQL code to the current SAS session's input stack. Executing the macro returns:

• the new key value, &newkey, for a new row.
• the current key value, &&&key, for an existing row.

One final note about this example: it takes advantage of functionality introduced in the M1 realease of 9.3. In the M0 release, macro variables created in the code called by DOSUB/DOSUBL are not available. Returning macro variables and their values was added with the M1 release.

DOSUB and DOSUBL - Data Driven Development

I have always been a fan of data driven applications where data (including paramater files) drives or defines the code to be executed. The DOSUB and DOSUBL functions, experimental in SAS 9.3, are a great addition to the toolset available in SAS to build data driven applications.  In my (upcoming) ebook (SAS® Server Pages: Generating Dynamic Content), there are lots of examples that use these functions so SAS code can be executed from a SAS Server Page. Both functions have a single character argument:

1. The argument to DOSUB is a fileref that point to the code to be executed.
2. The argument to DOSUBL is the line (or lines) of code to be executed.

What I would like to describe here is how they can be used for data driven development. In Chapter 4 of my book there is an example of a mail-merge application. This is a very simple example of data driven development: for each observation in a SAS data set run some code to create a letter or a report. The logic is fairly straightforward:

1. Determine how many observations there are in the input data set.
2. Use a macro to loop from 1 to the number of observations and do the following in each iteration
1. Read the ith observation
2. Load the values of the needed variables into macro variables
3. Invoke PROC STREAM to process a SAS Server Page that references the macro variables in the text of the letter

In other words, we use macro to run some code for each observation in a SAS data set The input SAS Server Page and one sample generated letter are shown below in Figures 1 and 2.

Figure 1. Input SAS Server Page

Figure 2. Generated Letter for John

What DOSUB and DOSUBL allow us to do is is to invert this process. For each observation in an input SAS data set, we run some code. So the DATA step become the driver instead of a Macro Language DO loop. The following program demonstrates this approach using the DOSUBL function.

proc format;
 /* map the value of sex to daughter/son */
 value $gender 'F' = 'daughter'
               'M' = 'son'
;
run;
data _null_;
 set sashelp.class;
 /* associate formats with sex and age */
 format sex $gender. age words.;
 /* create macro vars from the data step vars */
 /* vvalue uses the formatted value */
 call symputx('name',vvalue(name));
 call symputx('height',vvalue(height));
 call symputx('weight',vvalue(weight));
 call symputx('sex',vvalue(sex));
 call symputx('age',vvalue(age));
 /* define the code to run for each observation */
 code = 'filename letter "&root\letters\&name..html" '
      ||'lrecl=32767; '
      ||'proc stream outfile=letter quoting=both; '
      ||'begin '
      ||'&streamdelim; %include srvrpgs(class.html); '
      ||';;;;'
      ;
 /* run the code */
 rc = dosubl(code);
run;

Each execution of the  DATA step invokes PROC STREAM to generated the desired letter.

CALL EXECUTE vs DOSUB and DOSUBL

Like CALL EXECUTE, the DOSUB and DOSUBL functions allow you to generate code to be executed. However, unlike CALL EXECUTE, both DOSUB and DOSUBL execute the code the immediately while code passed to CALL EXECUTE is executed after the DATA step completes. In this example, if CALL EXECUTE had been used, all the generated letters would have used the values of the macro variables from the last observation in our input data set since each execution of the DATA step overwrites the macro variables. Since code passed to the DOSUB/DOSUBL functions is executed immediately, the values of the macro variables in our letter resolve the values from the current observation.

A Best Practice for the DOSUBL Argument

The length of the code stream passed to the DOSUBL routine can not exceed 32,767 characters. For the example included above, the code was included inline to simplify reviewing the example. As a Best Practice, code to be executed should not be included inline. Instead the code can be packaged as a macro and the macro call would be the argument to the DOSUBL routine. Alternatively it could be stored in an external file that is pointed to by a fileref and that fileref would be the argument to the DOSUB routine.

This Best Practice reinforces the paradigm shift when using DOSUB/DOSUBL routines. Instead of using the Macro Language to loop and execute mutiple DATA and/or PROC steps, we can now have the DATA step do the looping and execute a macro in each loop/iteration.

PROC SUMMARY - not just a summary tool

Yea, I know this sounds like an oxymoron. Why would you want to use PROC SUMMARY for anything other than summarizing data?

I would like to describe two examples where you can use PROC SUMMARY to create

1. A tree view menu - much like Windows Explorer does with folders and files.
2. Cascading select tags where the choices are dependent on previous choices.

A Tree View

In chapter 8 of my (upcoming) ebook (SAS® Server Pages: Generating Dynamic Content), there are examples of SAS Server Pages to create a tree view. Each of the examples uses a slightly different technique to create the needed input data set. Consider the data seen in Figure 1  (using the SASHELP.CLASS data set to make it easier for the reader to try out the code below). The data are arranged so the branches and leaves are nested under their respective parents.

For example:

Figure 1. Tree View Data Set



1. The top level nodes are observations 1(Female) and 16 (Male). This is reflected in the variable nodeText.
2. Observation 2 is the first Age value (eleven) for girls. Again reflected in the variable nodeText.
3. Next we see observation 3 for Joyce - an eleven year old girl.
4. And so on for the rest of the girls.
5. Next we can see that observation 17 is the first Age value for boys and Thomas at observaion 18 is the only eleven year old boy.
6. And so on.

This data set was created by a very simple SAS program:

proc summary data = sashelp.class;
 /* create the needed combinations */
 class sex age name;
 types sex
       sex*age
       sex*age*name;
 output out = treeview(drop=_type_ _freq_);
run;
proc sort data = treeview out = treeview;
 /* reorder the data so the branches and
    leaves are below the parent node */
 by sex age name;
run;
data treeview;
 /* create the menu node text */
 set treeview;
 format age words.;
 nodeText =
    coalescec(vvalue(name),vvalue(age),vvalue(sex));
run;

Figure 2. Tree View

The treeNodes SAS Server Page macro, when referenced in a SAS Server Page, will create the expanding/collapsing menu seen (and partially expanded) in Figure 2.

And two other points about the above code:

1. Notice the use of the coalescec function to get the value of the most detailed branch/leaf.
2. The vvalue function is used to return the formatted value. The use of this function, even if the variables don't have an assigned format, makes sure that all the arguments to the coalesec function are character (thus avoiding those pesky numeric to character conversion messages).

Cascading Select Tags

Conceptually a cascading select tag is simple. Each choice subsets the items in the next (or dependent) list to only include the dependent values for the current selection.

Creating such a data set is even easier that what is shown above. You just have to run the PROC SUMMARY step:

proc summary data = sashelp.class;
 /* create the needed combinations */
 class sex age name;
 types sex
       sex*age
       sex*age*name;
 output out = dependentSelect(drop=_type_ _freq_);
run;

Figure 3. Cascading Select Tag Data

Upon looking at the output data set (Figure 3), it is easy to see that the the various select tags can be constructed as follows:

1. Observations 1 and 2 are the values for the Sex select tag.
2. Observations 3 thru 7 are the values for the Age select tag when the selected value for Sex is F (note that there is no row for Age=16).
3. Observations 8 thru 13 are the values for the Age select tag when the selected value for Sex is M (note that there is a row for Age=16)
4. Observation 14 is the only value for the Name select tag for eleven year old girls.
   . . . .
5. Observations 24 thru 26 are the values for the Name select tag for twelve year old boys.
6. and so on . . . .

And, it should go without saying that the above code can be parameterized as a utility macro. But that is a topic for a future blog posting. As is a utility macro to generate the dependent select tags.

Error Handling in Utility Macros

Dealing with errors is always problematic. It can be particularly challenging in utility macros. Does every macro need to check for every possible error? Or should a developer assume some level of error checking has been done by the calling application, program or macro?

My view is that assuming a certain level of error checking by the calling application, program or macro is reasonable; but that one should still do a reasonable level of defensive programming.

For example, consider the generateOptionTag macro that I have mentioned in a number of posts. It creates a select tag from data in a SAS data set. But suppose the data set, or the variables for the coded value, or the label for the select tag, don't exist?

The minimalist defensive solution is to just exit the macro without generating the select tag. But the user is left with a user interface page that is incomplete/wrong. And since the user, in this case, is almost certainly not a developer, determining exactly what additional messages or diagnostics are appropriate can be challenging.

The approach that I like is to make sure the user knows that an error happened and, at the same time, give them enough information so they can alert the right folks.

Both the Stored Process Server and SAS/IntrNet Application Server programs will display a message to the user if an error is encountered. But since defensive programming has prevented a SAS error, the issue is how can we force that message without generating a SAS error? Reviewing the SAS knowledge base article Tips to remove the Show SAS log button if a SAS® Stored Process executes with an error suggests a simple solution: set the value of the SYSCC macro variable to a value larger that 4.

But we still need to make it easy for the user to find the error. That is where Using NOTE, WARNING, ERROR in Your Program's Generated Messages can help.  You can produce an error message that provides some details and since it will be highlighted in the SAS Log, it is easy to find.

So here is a typical snippet of code that illustrates this technique:

%let syscc=256;
%put ERROR: The specified data set, &data, is not available.;
%return;

The %return statement stops the currently executing macro and returns control to the calling macro or program.

And in a SAS Server Page utility macro that is generating a user interface, you can also generate a mailto hyperlink to make it easy for the user to report the error. Something like:

Email the <a href="mailto:developer-email?Subject=Error Message">developer</a> about the error.

can make it easy for the user to report the error.

Remembering User Choices

In my Chapter 2 of my ebook (SAS® Server Pages: Generating Dynamic Content) I show several examples of reporting functionality that use Stored Processes and SAS Server Pages on the SAS Portal where the user's choices are saved. As shown in the example screen shot, upon logging into the Portal and then selecting the reporting tab, the user's last report choice is remembered and submitted. The box on the left is a select tag (generated by the generateOptionTag utility macro) that uses the size option so multiple choices are displayed. This select tag is populated using the parms.report_list_view data set. The variable Report_Key is the value of the select tag and Report_Description is the label. Thus, remembering the user's choice is simply a matter of saving the value of Report_Key on the server.

The following simple data step saves the user's report choice so that it is available later in the same browser session; as well as the next time they log in (even if it is days later).

data profiles.&_metauser._report;
  Report_Key=&Report_Key;
run;

This code is included in the report driver macro so that whenever the user makes a choice, it is saved. The Stored Process Server provided macro variable _metauser is used to identify the user (note that if the SAS/IntrNet Application Dispatcher is being used, _rmtuser would be used instead of _metauser).

Using the following utility macro in the SAS Server Page that generates the UI allows the page to remember the user's last choice.:

%macro getReportKey;
 %local dsid rc;
 %let dsid=%sysfunc(open(profiles.&_metauser._report));
 %if &dsid=0 %then %return; /*no saved choices dataset*/
 %let rc=%sysfunc(fetch(&dsid));
 %if &rc=0 %then %sysfunc(getvarn(&dsid,1)); /* data set has a saved choice */
 %let dsid=%sysfunc(close(&dsid));
%mend getReportKey;

And then you only need to include code like what is shown below in the SAS Server Page:

%let Report_Key = %getReportKey;
.
.
.
%generateOptionTag
        (data = parms.report_list_view
        ,var = _report_key
        ,name = report_key
        ,selected = &report_key
        ,label = report_description
        ,otherOptions = size=30 onchange="submit();"
        )

That is really all there is to it.

Needless to say there are lots of variations and extensions of this technique:

• This example has a dedicated data library (profiles) that has one data set for each user. A database or SAS/SHARE could be used so that the data is stored in a single data set with a row for each user.
• Multiple data sets can be saved for each user if different kinds of data, or multiple values, need to be saved (in a future blog posting I will discuss the example in my book that uses multiple checkboxes, instead of a select tag). And, of course, there are any number of alternative data structures that could be used.
• If the user id (_metauser) has special characters in it (e.g., /, \ or @), some siimple macro logic could be used to map it to a unique value that is a valid SAS name.
• The macro could be paramaterized (I kept it simple for this example).

I have also used this technique with Web Report Studio using an Information Map as discussed in this example so that the user's choices are remembered for Web Report Studio reports. This can be particularly helpful when there are multiple reports and users don't want to have to reselect the report parameters for each report.