PROC STREAM: Extending the Macro Language to create more than just SAS code

The STREAM procedure is a new experimental procedure available in SAS 9.3. It processes a SAS generated input stream, including macro specifications and logic and directs the generated text to any fileref. It provides direct support for SAS Server Pages, as initially described in my SAS Press book, Building Web Applications with SAS/IntrNet®: A Guide to the Application Dispatcher.  PROC STREAM significantly expands those capabilities in a number of ways:

1. There is no 32K limit on the text produced by a single input line in a SAS Server Page.
2. A much broader range of SAS statements can now be used, including macro definitions and %include statements.
3. SAS code can be embedded and executed from within a SAS Server Page.
4. The %sysfunc macro can invoke the SCL functions that access data.
5. and more

PROC STREAM can be used with the macro language in order to produce virtually any data driven text file, including but not limited to:

1. HTML Reports and UIs.
2. Word documents (as RTF files).
3. XML documents
4. XAML/Silverlight documents

The bottom line is that this facility can be used for more than just creating SAS Server Pages. It will be the subject of an upcoming e-Book, SAS Server Pages and More: A Framework for Generating Dynamic Content, that will be published by SAS Press. Please use the discussion tab on sasCommunity.org to ask any questions or suggest topics/examples for the e-Book.

If you have SAS 9.3, please feel free to try out the following example that just scratches the surface of what this powerful new procedure can do.

filename sspout '\PROC_STREAM_says_hello.html';
proc stream outfile=sspout sqac dqac;
BEGIN
%macro checkTOD;
%local timeOfDay;
%let timeOfDay = %sysfunc(time());
%if &timeOfDay le 43200 %then Morning;
%else %if &timeOfDay le 64800 %then Afternoon;
%else %if &timeOfDay le 72000 %then Evening;
%else Night;
%mend checkTOD;
<h1>Good %checkTOD &sysuserid..</h1>
<h2> This welcome note generated
 at %sysfunc(time(),timeampm8.)
 on %sysfunc(date(),worddate.).</h2>
This HTML file was produced and customized
 courtesy of PROC STREAM using
 SAS Release &sysver on &sysscp..;
;;;;
dm "wbrowse '\PROC_STREAM_says_hello.html'";

Using formulas in Excel and actual values in HTML and PDF output

On a recent project I had to use the technique I described in an earlier post to create HTML, PDF, and Excel (using the ExcelXP tagset) output all at the same time. Adding ExcelXP output was straightforward. But, of course, there was a wrinkle. The Excel version had to use formulas so the user could do some what-if analysis. So I faced what I thought was a serious problem - how to put the actual values in the PDF and HTML versions, but with formulas in Excel.

But luckily the ExcelXP wizards at SAS (and by that I mean Eric Gebhart and Vince DelGobbo) provided a way to do just that! I am not sure if this use-case was part of their design/approach. But it worked. They use the tagattr attribute to do all sorts of cool things - like providing a formula. Here is a simple example that generates (all at once) HTML, PDF and Excel versions of the report with the static values in PDF and HTML, but formulas in Excel.

options nodate nonumber;
ods listing close;
ods html file='\Formulas.html';
ods pdf file='\Formulas.pdf' notoc;
ods tagsets.ExcelXp file='\Formulas.xml';
proc report data = sashelp.class nowd;
 title 'HTML, PDF, and ExcelXP, with Formulas in Excel';
 columns name age sex height weight bmi;
 define bmi / computed format=5.2 style=
   [tagattr='(formula:RC[-1]*703)/(RC[-2]^2) format:0.00'];
 compute bmi;
   bmi = (weight.sum*703)/(height.sum**2);
 endcomp;
run;
ods _all_ close;

A few points about this:

1. Note that the formula uses the Excel R1C1 notation (and it works regardless of whether this reference style is enabled in Excel).
1. RC[-1] uses the value from the same row, one column to the left.
2. RC[-2] uses the value from the same row, two columns to the left.
3. So the formula uses the current row's prior column value (i.e., one column to the left), multiplies it by 703 and then divides that by the square of the value in current row two columns to the left.
2. Since Excel chooses to do its own thing with formats, the format attribute is used to tell Excel how to format the value.
3. Note the use of ^  instead of ** for the exponentiation operator.

Since the program uses the SASHELP.CLASS data, which luckily has the needed components to calculate Body Mass Index (aka BMI), you can run this yourself.

PUT Statements in a Workspace Server to generate HTML? Yes you can!

Well, for years I've operated under the assumption that because a SAS Workspace Server (WS) does not support streaming HTML, that a stored process run by a WS could not use DATA steps to write HTML to the user's browser. Because the _webout fileref is not defined, you have to use packages.

Why does this matter? Why not just use a SAS Stored Process Server? Well, there are a multitude of reasons for this, but here are a few use cases that in prior projects have led me to want to use a WS:

• A WS is started for each client request and thus it is running using the user's credentials. That can be important if you want to control file system access.
• If a stored process needs to run for a long time (and that is a relative term in the Web world), using a WS does not cause the up and running Stored Process Servers to be tied up with such requests. Using a Stored Process Server for such requests limits access to them for all other user requests (this, of course, also depends on how your pool is set up).

You can read more about the differences at support.sas.com.

Because WS use packages, the question then becomes how do you set up the environment so that you have a fileref to write your HTML (as well as other content types) to so that it will be directly displayed by the browser. With some help from Vince @ SAS, it turns out that with a few tweaks in how you call the stpBegin and stpEnd macros, you can do that. The trick is to set things up so that stpBegin and stpEnd create a package, but do not issue an ODS statement.

Here are a few simple (in hindsight) things you need to do:

• Before calling the stpBegin macro, make sure it does not create any ODS statements by setting the value of the _odsDest macro variable to NONE. You can do this by making it a parameter to the stored process, or by using a %let statement.
• Then after the stpBegin macro call, simply set the magic macro variable _NAMEVALUE to set the _DEFAULT_ENTRY attribute which specifies the name of the entry in the package that will be displayed to the user. For example:
    %let outfile=streaming.html;
    %let _NAMEVALUE=_DEFAULT_ENTRY=&outfile;
  Note that the macro variable outfile is used because this value will be used in multiple locations in your stored process.
• Then issue a filename statement to create a fileref that you can use in your DATA Step code:
    filename _webout "&_stpwork&outfile";

That's all there is to it. Your package can contain any number of files, but the _DEFAULT_ENTRY attribute specifies which one will be displayed by the package viewer.

Here is a complete example that is a simplified version of SAS Server Pages that you can try out for yourself. Note that because the name/location of the output file had to be referenced in two places, the code uses a macro variable to ensure the values are consistent.

*ProcessBody;

%let outfile=streaming.html;
%let _odsDest=NONE;
%stpBegin()

%let _namevalue=_DEFAULT_ENTRY=&outfile;
filename _webout "&_stpwork&outfile";

data _null_;
infile datalines;
file _webout;
input;
_infile_ = resolve(_infile_);
put _infile_;
datalines;
<h1>Testing a WS running as Process ID &sysjobid
for User &sysuserid</h1>
This test was run at
%sysfunc(time(),timeampm.)
on
%sysfunc(date(),worddate.)
;
run;

%stpEnd()

This Page Intentionally Left Blank

Sometimes printed documents will have a message like This Page Intentionally Left Blank. But, of course, since those pages have that message, they really aren't blank.

How can we adapt this to our reporting programs and processes?

• In a BI environment if, for example, a Stored Process report has no data, then the user will either see a blank page or an error message that no output was generated.
• In a batch reporting environment where reports are emailed as attachments, if there is no data, the file may not be created and so the code to email the report will likely fail.

Neither of these is particularly user-friendly or desirable. So, how do we handle this? This Page Intentionally Left Blank suggests a solution. Simply produce some output. The macro noDataFoundMessage does just that for you. If the input data set is empty, it produces a page of output with an appropriate message. If the output data set is not empty, it generates no output. When used with your reporting code you are thus guaranteed that some output is produced

• if you have data, your report is output, but the noDataFoundMessage macro produces no output
• if there is no data, your report code produces no output, but the noDataFoundMessage does

So the trick is call both your reporting code and the noDataFoundMessage macro. One of the two, but not both, will produce output.

The macro source follows. Feel free to use and share it. I just ask that it's source is acknowledged.

%macro noDataFoundMessage
     (Data=_last_,
      Message=No Data Found
     );

/*----------------------------------------------
Copyright (c) 2008 Henderson Consulting Services
PROGRAMMER : Don Henderson
PURPOSE : Generates a message if the specified
          input data set is empty.

Used to prevent the SAS Stored Process Server
from returning the message that no output was
generated for situations where there is no data.

Can be called immediately after reporting code,
using the same input data set, to produce the
custom message if the input data is empty.
---------------------------------------------*/

data nodata;
length msg $128;
if lr then
do; /* no data */
   msg = symget("Message");
   output;
end; /* no data */
set &data end=lr;
stop;
run;

proc report data = nodata nowd;
 columns msg;
 define msg / display ' ';
run;

%mend noDataFoundMessage;