More on the streamDelim Macro Variable

Based on my last post (Processing External Files with PROC STREAM) you may now be curious about the rationale for the streamDelim macro variable as well as what else you can do with it.

But first, an announcement: The sasCommunity page SAS® Server Pages: Generating Dynamic Content has been updated to include:

• The hopefully final title of the eBook
• The list of chapters and topics covered
• A link to the preview copy of Chapter 1 at the SAS press site (which provides more details on the book's content)
• A list of my blog entries about SAS Server Pages (which will be regularly updated).

Please check it out.

Now back to streamDelim - why is it needed and what else can it be used for?

In working with Rick Langston of SAS R&D on this, one of the features we really wanted to support was the ability to use %include to include an external file into a SAS Server Page. In order to allow %include to be recognized and handled correctly it needs to be on a statement boundary - i.e., immediately after a semi-colon. So a way was needed to force a statement boundary without having the semi-colon appear in the output. The led to the resetDelim option (in the TS1M0 and TS1M1 releases of SAS 9.3):

proc stream . . . . resetDelim="a_SAS_name_token";

where you specify some text as a delimiter that does not occur in your input SAS Server Pages. This means that the following text could be used to allow %include to be recognized:

a_SAS_name_token; %include fileref-or-path-to-file;

but doing it that way allowed for the possibility of inconsistent values between the input SAS Server Page and the PROC STREAM statement (i.e., a SAS Server Page could be created or edited and not have the same value, or vice-versa). To address this, the TS1M2 release uses the value of a macro variable, streamDelim, as the delimiter. And if this macro variable does not exist, PROC STREAM creates it.

The resetDelim option still exists and that is why the following technique (mentioned in my last blog posting) works:

%let streamDelim = __&sysfunc(datetime(),z18.);
proc stream . . . . resetDelim = "&streamDelim";

So what else can you do with streamDelim?

You can use the following text in your SAS Server Page to force a new line or line break:

&streamDelim newline;

The tokenization process that PROC STREAM uses to resolve macro references ignores/loses line breaks (as many macro programmers know). There are any number of reasons you might want to force going to a new line, e.g.,:

• readability of the generated text
• to prevent line breaks forced by the output LRECL in places that might introduce errors (e.g., in a long select tag)
• to deal with // style JavaScript comments (which says that all the rest of the text on the current line is a comment)

Another reason is that you might want to include a file - but you don't want it submit it to the resolution process. The readfile parameter will do this , e.g.:

&streamDelim readfile fileref-or-path-to-file;

And again, there are multiple reasons for not wanted resolution to occur:

• no macro resolution is needed or desired
• proper handling of JavaScript - && is the JavaScript AND operator. Macro resolution will convert && to & causing the JavaScript to not function correctly

Future blog posts will talk about when and how to use these features.

Processing External Files with PROC STREAM

In the examples posted in previous blog entries (A Gentle Introduction to SAS Server Pages and PROC STREAM: Extending the Macro Language to create more than just SAS code), the input text being processed was included in the SAS job stream and was delimited by:

• the token BEGIN (case insensitive)
• four semicolons with no intervening spaces starting in column 1 (;;;;)

PROC STREAM utilizes the SAS word-scanner and tokenization facilities to resolve and execute macro variable references, macro functions and macro calls for all the text delimited by BEGIN .... ;;;; and directs the output to a specified external file:

proc stream outfile= ... ;
BEGIN
/* Input text to be processed */
;;;;
run;

Quite often the text that you will want PROC STREAM to process is contained in an external file (what I would refer to as a SAS Server Page - text, e.g., HTML, along with commands interpreted by SAS to generate additional data-driven content). So the question becomes how to do that since there in no infile option. And the answer is to use a %INCLUDE statement. PROC STREAM recognizes the %INCLUDE statement and will use the contents of that file as its input. So our PROC STREAM statement looks like this:

proc stream outfile= ... ;
BEGIN
/* Include text file to be processed */
&streamDelim; %include fileref-or-path-to-file;
;;;;

Note that fileref-or-path-to-file can reference a fileref, a physical path or use SAS aggregate syntax. I prefer to use aggregate syntax as typically my SAS Server Pages are organized in one or more directories (and you can define your fileref so it points to concatenated directories).

The content of the file being included need not be SAS code, it can be any text (e.g., HTML, XML, CSV, SAS code, and more) - whatever text it contains will be processed by the SAS tokenizer.

But now you ask, what it &streamDelim; and why is it there? It is a delimiter that is needed because certain SAS statements must appear on statement boundaries (i.e., they must be the very first statement in your program or they must immediately follow a semi-colon). So in order for PROC STREAM to recognize the %INCLUDE, it needs to follow a semicolon. But since you typically don't want the ; in the output file, we need to have a way to tell PROC STREAM to ignore it - thus &streamDelim. There are a number of other things you can do with &streamDelim - and I'll have examples and blog postings between now and SAS Global Forum.

NOTE: In the SAS TS1M2 release, PROC STREAM will create the &streamDelim macro variable if it does not already exist. Until then, you can slightly modify the syntax above as follows to create a value for streamDelim that is a valid SAS name token but whose text value is not in your input SAS Server Page, for example:

%let streamDelim = __&sysfunc(datetime(),z18.)
proc stream . . . . resetDelim = "&streamDelim";
/* Include text file to be processed */
BEGIN
&streamDelim; %include srvrpgs(HelloWorld.html);
;;;;

Input HTML (and other) files may have an additional wrinkle - named HTML Entities. For example, the HelloWorld.html file contains ® for the registered trademark symbol in the text:

. . . input SAS® Server Page . . .

When this text is processed we will likely get a warning, or depending on the context, an error message for &reg since the SAS tokenizer will interpret it as a macro variable reference. However, the comparable numeric HTML Entity (®) for the registered trademark symbol does not have this problem since the SAS tokenizer does not see #174 as a macro variable reference. In order to avoid editing the input files to convert named to numeric HTML Entities, we can let the SAS tokenizer do the work for us. Since the content of the input file is being tokenized and macro variable references are replaced with their values, including a series of %let statements like the following before invoking PROC STREAM for the standard HTML entities

%let reg = ®

will allow the tokenizer to do the substitutions for us: &reg will be replaced by &#174 and so ® will be resolved to ®.

You can see both %include and this substitution in action on my server using:

• the SAS/IntrNet Application Dispatcher
• the Stored Process Server

both of which use the same code and the same input SAS Server Page (our Hello World example).

I will have more examples in future blog entries (and, of course, in the book) that take advantage of %INCLUDE, including input SAS Server Pages that have %INCLUDE statements.

A Gentle Introduction to SAS Server Pages

My last post PROC STREAM: Extending the Macro Language to create more than just SAS code introduced the 9.3 experimental procedure, PROC STREAM, which provides direct support for SAS Server Pages. It also provided a very preliminary preview of a SAS Press eBook on PROC STREAM and SAS Server Pages. A free preview copy of selected chapters is targeted for the SAS Global Forum 2012 timeframe - look for a blog posting soon with more details on the topics covered in the eBook and the preview version.

Between now and SAS Global Forum I plan to write a number of blog entries on this topic - starting with an overview and a brief overview of what SAS Server Pages are. I will also be providing online demos so that even if you don't have access to SAS 9.3, you can see the generated output. I will be providing links the run the examples using both the SAS/IntrNet Application Dispatcher as well as the Stored Process Server. And note that the exact same programs are used by both the SAS/IntrNet Application Dispatcher and the Stored Process Server: one program for the DATA Step example; and one for the PROC STREAM example.

As discussed on sasCommunity.org, SAS Server Pages can be generated using the RESOLVE function in a DATA Step. So let's look at how to do that.

Here is a variation of the simple example in my last blog post:

data _null_;
file _webout;
infile datalines;
input;
_infile_ = resolve(_infile_);
put _infile_;
datalines4;
<html>
<head><title>The Obligatory Hello World Example</title></head>
<body>
<h1>Hello &_rmtuser..</h1>
<h2> This welcome note generated
at %sysfunc(time(),timeampm8.)¹
on %sysfunc(date(),worddate.).</h2>
This HTML file was produced from an input
SAS Server Page and customized courtesy
of a DATA Step and the RESOLVE function
using SAS Release &sysver on &sysscp..
<p>¹The time listed is the server
time - the US Rocky Mountain time zone.
</body>
</html>
;;;;

The RESOLVE function is used to resolve macro variable references and execute macros (none included in this example) as well as macro functions (e.g., the %sysfunc macro fucntion). Try this out on my server:

• SAS/IntrNet Application Dispatcher
• Stored Process Server 

Now lets look at the PROC STREAM version:

proc stream outfile=_webout quoting=both;

BEGIN
<html>
<head><title>The Obigatory Hello World Example</title></head>
<body>
<h1>Hello &_rmtuser..</h1>
<h2> This welcome note generated
at %sysfunc(time(),timeampm8.)¹
on %sysfunc(date(),worddate.).</h2>
This HTML file was produced from an input
SAS Server Page and customized courtesy
of PROC STREAM
using SAS Release &sysver on &sysscp..
<p>¹The time listed is the server
time - the US Rocky Mountain time zone.
</body>
</html>
;;;;

You can run these out on my server as well:

• SAS/IntrNet Application Dispatcher
• Stored Process Server

The DATA Step and PROC STREAM both processed the same input SAS Server Page - and both produced the same results. PROC STREAM includes a number of features and capabilities that can't be done with the DATA Step approach. I'll be highlighting a number of those features in blog posts between now and SAS Global Forum.

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;