Generating Descriptive Please Wait Messages for Long Running Stored Processes

Well, its been a while since I posted to my blog. And to acknowledge this, I choose a topic that is all about being patient ;-).

Most everyone who has built a web application that takes more than a split second to run knows that users want immeditate feedback. That is why many sites generating spinning logos (and other amusing things) to alert users that progress is being made.

Such functionality is fairly straigtforward to implement in SAS Stored Processes. So here goes.

First check out an example, running on my server. This example interleaves a DATA step containing a SLEEP function call with a call to my pleaseWait macro.

So how'd I do that? It was actually not that hard. I took advantage of some HTML style/display attributes that can be controlled thru JavaScript combined with the SAS ODS HTML TEXT= statement. Here is a little code snippet from my pleaseWait macro that illustrates how it is done:

ods html text = "<span id=""pleaseWait&pleaseWaitCounter"">";
proc report data = pleaseWait nowd;
title "&message";
columns msg;
define msg / " ";
run;
ods html text = '</span>';
Some details:

1. The pleaseWait SAS data set is a single observation SAS data set where the informative message is the value of the macro variable &message.


2. The value of the data step variable msg is the logo I want to display.


3. The macro variable reference &pleaseWaitCounter is used to allow for multiple status messages to be generated with each message replacing the prior one.


4. I tried to do this all with the ODS HTML TEXT (i.e. without a PROC step), but it seems that ODS output in not streamed back to browser until there is a DATA and/or PROC step.

Check out the article on sasCommunity.org for the source code for the macro and the sample call. Feel free to comment here or on the sasCommunity discussion page if you have questions or want more details.

HTML, PDF, RTF (and more) all at the same time.

or . . . when is a session not really a session.

In my last post I illustrated explicity creating and using a session. Of course as with all things SAS related, that is not the only way to create a session!

In order for ODS to support creating html page with mixed text and graphics (e.g., a PROC PRINT and a PROC GCHART), ODS uses lightweight sessions. These are sessions without a lot of the overhead of a full session, but which allow a program to write some content, e.g., in the case of ODS, perhaps a graph where the generated HTML contains a link that replays the graph. So while it looks to the user like they are getting a table and graph in one request, there are actually two requests. Run this sample Table and Graph and take a look at the generated HTML for the img tag.

Note that this example is actually a SAS/IntrNet sample from my SAS Press Book . . . . . . but the Stored Process Server works the same way!

The generated link uses the replay program to do that. Upon a review of the generated HTML (just do a View Source in your browser) you see that the replayed graph is actually an entry in a SAS catalog. Well, it turns out that whenever you submit a request for the Stored Process Server to run a program, SAS creates a catalog and the catalog name is available to your program as the macro variable reference &_tmpcat.

And now it gets even more interesting because simply by writing something to that catalog causes SAS to create the lightweight session.

Now lets consider the case where we want to generate an HTML report, but you also want to provide a way for a user to access a printable version of the report (e.g., a PDF file) or an easily editable version (e.g., an RTF file) without having to rerun the reporting code. A lightweight session can be used to so that ODS will write PDF and RTF versions to the &_tmpcat catalog while generating/streaming the HTML version back to the users browser.

Just include statements like this in your program:

  filename pd catalog "&_tmpcat..shoes.pdf";
  filename rt catalog "&_tmpcat..shoes.rtf";

and then use the stpBegin macro for the HTML version and explicit ODS statements for the PDF and RTF versions:

  %stpBegin
  ods pdf file = pd style = sasweb notoc;
  ods rtf file = rt style=sasweb;

then include whatever your reporting code is (e.g., PRINT, TABULATE, REPORT, etc.).

We now need to include some HTML to provide the hyperlinks to that will replay the PDF and/or RTF versions. The following DATA steps creates macro variables whose values are the needed HTML:

  data _null_;
   call symput('replayPDF','''<center><a href="'||&_replay||'shoes.pdf">Printable (PDF)</a></center>''');
   call symput('replayRTF','''<center><a href="'||&_replay||'shoes.rtf">Editable (RTF)</a></center>''');
  run;

and, yea, I know the quoting is UGLY. I promise I'll blog about better ways to do this at some point in the future ;-).

and these ODS HTML statements generate the needed links in the HTML version.

  ods html text = &replayPDF;
  ods html text = &replayRTF;

And, as a bonus, since the ODS HTML statement does not write any content to the PDF or ODS destinations, those versions of the reports do not contain the hyperlink text!

And since the %stpEnd macro generates an

  ODS _ALL_ close;

statement, it will close all of the output destinations for you.

Feel free to check out this sample on my server.

And, of course, there are more examples of sessions and lightweight sessions in Building Web Applications with SAS/IntrNet®: A Guide to the Application Dispatcher. And they work pretty much the same way in the Stored Process Server (with the exception of the one issue highlighted in my PRIOR POSTING).

Happy Sessioning ;-).

Sessions - A double edged sword

In my previous post (which was longer ago than I'd like - but maybe I can blame the US East Coast Blizzards) I blogged about using Sessions as a surrogate for some of the things we old-style SAS programmers used the WORK library for.

One of the things that many of us overlook when delivering SAS content over the web is that each request has to be quick - users get very impatient if they have to wait too long. So long-running programs with lots of steps should not just be packaged up as Stored Processes. They need to be broken down into pieces/parts that can be run sequentially - just like one can run a series of DATA/PROC steps in an interactive SAS session. And that is where sessions can come into play - we can use a session to save data sets (and macro variables) from one request to the next). So the Sessions SAVE library becomes the replacement/alternative for WORK.

So here goes with a little tutorial on creating a Stored Process that uses sessions. Let me begin by saying that I will of course be packaging this as a macro so I can leverage my runMacro Stored Process.

The following macro code shows a simple template for how to use sessions. Note how the sysfunc macro is used to invoke the libref function which checks if the SAVE library exists. Since the SAVE library is created when the sesssion is created and is made available when reconnecting to a session, that is a convenient way to check if we already have a session.

%macro sessionsExample;
 %global save_Session_Created;
 %if %sysfunc(libref(save)) ne 0 %then
 %do;  /* session does not exist - create it */

 %end;  /* session does not exist - create it */
 %else
 %do;  /* reconnecting to the session */

 %end; /* reconnecting to the session */
%mend sessionsExample;

In the first %do-%end block we add some code to create a session:

 %let rc = %sysfunc(stpsrv_session(create));

and create a macro variable to be saved (note the prefix save_)

 %let save_Session_Created = %sysfunc(datetime(),datetime.);

and, in this case some sample code to perhaps access a large table and summarize it - so we only have to do that once (and please forgive me for cheating :-) here and using a small SASHELP data set).

proc summary data = sashelp.shoes nway;
 class product;
 var stores sales returns inventory;
 output out=save.SummaryTable(drop=_freq_ _type_) sum=;
run;

We then list the data and use a title to generate a hyperlink to reconnect to the session.

proc print data = save.SummaryTable;
 title "Session Created at &save_Session_Created";
 title2 '<a href="' "&_thisSession" '&_program=' "&_program" '&macroToRun=' "&macroTorun" '">Reconnect</a>';
run;

The macro variable _thisSession is the key here - this is what causes the request to connect back to the same session as described in this SAS 9.1.3 Doc. (and it works pretty much the same in 9.2)

And in the code where we are reconnecting, we can access the macro variables and the data we saved in the SAVE library, e.g.,

proc report data = save.SummaryTable;
 columns product stores sales returns inventory;
 rbreak after / summarize;
 title "Reconnecting at %sysfunc(datetime(),datetime.) to Session Created at &save_Session_Created";
 footnote '<a href="' "&_thisSession" '&_program=' "&_program" '&macroToRun=' "&macroTorun" '">Reconnect Again</a>
run;

Feel free to try it on on my server.

There are lots of other example of sessions described in my SAS Press Book. They work pretty much the same way in the SAS 9 Stored Process server with a few minor differences described on this page about upgrading SAS/IntrNet program to Stored Processes (the most notable is the format change for the _REPLAY macro variable).

So by now, if you made it this far :-), you are probably confused by the reference to the double edged sword. Check out the limitations at the bottom of this page - overuse of sessions can seriously impact performance. So only use em when you need em!

WORK doesn't work the same way

So hows that for a double entendre?

One of the biggest challenges traditional SAS users have trouble with is the fact that WORK does not work the same way in BI/Web applications. Such SAS users are used to the work library containing all sorts of data sets and catalogs that are easily accessible later in the program (either in an interactive SAS session or a long batch job). Most BI tasks however are (and should be) structured as a series of short tasks. This is exactly where some SAS users get tripped up when building BI/Web based reports - each request starts with a brand new clean/emply WORK library. This is because the Web is a stateless environment – each request knows nothing about any prior request. This creates a simple environment in that each request is completely self contained. However, quite often it is desirable and necessary to maintain certain information from one request to the next (i.e. do what the WORK library does). This is known as maintaining state.

The good news is that that there are a number of ways to deal with this. The Stored Process Server (and the SAS/IntrNet Application Dispatcher) support the creation and use of Sessions as a way to share data and macro variables from one request to the next. A Session is simply the saving of data and parameters from one request to the next. Sessions provide the ability to save library members (data sets and catalogs) and macro variables. The program explicitly specifies what to save and restore.

The mechanics of using Sessions are straightforward. Once a session has been created, a library named SAVE is created. By creating or copying data sets and catalogs to this library, the user program can rely on them being there the next time a request is made that uses this session. For global macro variables, at the completion of any request that uses sessions, all the global macro variables whose name begin with SAVE_ are saved. At the beginning of any future request, they are restored.

And sometimes SAS will create sessions automatically. For example, when ODS is used to create an HTML page containing a graph, ODS will create a lightweight session. Since a single web request can only return one file, ODS will create the additional files and save them in SAS catalog entries. For an HTML page with an embedded graph, the graph (or graphs) will be created and saved as a graphic (e.g., gif or jpeg) entry in a catalog. For a frame, the table of contents file and the body file are saved in html entries in such a catalog. These entries are returned to the user’s browser by a link that reconnects to the session to replay the entry.

Check back later for some examples on creating and using sessions:

• the mechanics of creation a session
• using lightweight sessions so you can generate, for example, HTML, PDF and RTF output in one request
• creating a session to extract/summarize data once so multiple reports can be generated from it.

An autoexec facility for the Stored Process Server

The SAS 9.2 Stored Process Server includes an option to specify the name of a SAS program that is to be run before any request - this is conceptually similar to the autoexec facility that has existed in SAS back to it's Jurassic days.

And, as an added bonus, there is also an option to specify the name of a program to run after each stored process. This facility provides the facility to do many of the functions described in Chapter 9 of my SAS Press Book, Building Web Applications with SAS/IntrNet®: A Guide to the Application Dispatcher, such as defining the list of available libraries based on who the user is, etc.

NOTE: Do NOT try the examples that utilize ENDSAS; as that statement does not do the same thing in the Stored Process Server as it did in the SAS/IntrNet Application Dispatcher where it simply ended the current request. In the Stored Process Server it will shut down the Stored Process Server itself.

Just follow these simple instructions to specify the program to run before/after each Stored Process.

When was this report generated?

It is almost always a good idea to include in any generated output report some indication of when the report was generated. Not only does it provide context, but when viewing reports generated dynamically by, say, the SAS/IntrNet Application Dispatcher or the Stored Process server, it can also help to confirm that the user is viewing a current version rather than a version that has been cached by, say, their browser or a proxy server.

SAS provides a number of tools to include the date and/or time in the generated output. I like to include the values in a footnote and use the %sysfunc function to get the current date and/or time. And, of course, for the sake of consistency and ease of use, I package this code as a macro.

Here is a sample macro:

%macro generatedAt
(text=Generated At,
datefmt=worddate.,
timefmt=timeampm7.,
separator=on
);

%local dt tm;
%if %superQ(datefmt) ne %then %let dt = %sysfunc(date(),&datefmt);
%if %superQ(timefmt) ne %then %let tm = %sysfunc(time(),&timefmt);

&text &tm &separator &dt

%mend generatedAt;

The macro generates text that can be used in a title or footnote statement; or even in a put statement. This version of the macro allows you to specifiy:

• text: the text to display before the date/time
• datefmt: the SAS date format to use for the date format. If a null/blank value is specified, the date is not included
• timefmt: the SAS time format to use for the time format. If a null/blank value is specified, the time is not included
• separator: the text to separate the date and time display

There are lots of other options that one could consider (e.g., list the the date and then the time or vice versa), but I've found that most of my clients want consistency and so I simply the customize the macro to generate the text to be exactly the way they want it.

Now all I have to do is include the macro call in, for example, a footnote statement:

proc print data = sashelp.class;
title 'The ever popular CLASS data set';
footnote j=right h=1 "%generatedAt";
run;

Note the user of the j (justify) and h (height) attributes. Check out Controlling text height in titles and footnotes for some other examples of controlling the format in title and footnote statements.

Useful parameters/extensions for the runMacro Stored Process

Before leaving the runMacro Stored Process and moving on to other topics, lets add some features to our Stored Process. In Dressing Up PROC REPORT we saw how the SAS TableEditor tagset added a lot of capability to our reports. So lets add support for the TableEditor tagset, the ExcelXP tagset, and simple HTML displayed as if it were an Excel file.

The stpBegin macro does a lot for us, but for some of the customizations we will need to help it out a little by adding some logic in a DATA step before calling stpBegin.

For HTML opened in Excel, we just need to make sure that the Content-type header for Excel is used, e.g.,

rc = stpsrv_header('content-type','application/vnd.ms-excel');

We will do this by specifying a value of EXCEL for _odsDest (which we reset to HTML after generating the Content-type header).

We need the same Content-type header for ExcelXP. But due to an issue with how Office 2007 works, we also need to provide a Content-disposition header that specifies it is an XML file, e.g.,

rc = stpsrv_header('Content-disposition','attachment; filename="_.xml"');

Note the use of _.xml as the filename. Our generic Stored Process can't infer a reasonable name, so we just use the _. Of course, you could make this a parameter - but that is left as exercise for the reader ;-).

For the TableEditor tagset, we just need to pass a few options into the stpBegin macro using the global macro variable _odsOptions. There are lots of parameters the we could use. For the purposes of this example, a few that you will almost certainly want to support are:

1. pageheight
2. pagewidth
3. sort
4. frozen_rowheaders
5. frozen_headers

Just create parameters for each of these and then reference the values as macro variables. You should also make sure to specify default values for the Stored Process parameters. The following code will append the values specified to the existing value of _odsOptions (which could also be a Stored Process parameter).

call symput
    ('_odsOptions',
        ("options(" ||
    || strip(symget('_odsOptions')
    || " pageheight='&pageheight'"
    || " pagewidth='&pagewidth'"
    || " sort='&sort'"
    || " frozen_rowheaders='&frozen_rowheaders'"
    || " frozen_headers='&frozen_headers')"));

The complete runMacro Stored Process can be found on sasCommunity.

A Home for my runMacro Stored Process

In my last post I talked about my runMacro Stored Process and one approach for storing/managing the macro code. Now it is time to talk about where to store the stored process code itself.

In the SAS Metadata (using SAS Management Console, aka SMC) you can define a logical hierarchy for your Stored Processes which is completely independent of how/where they are physically stored.

For the physical location on the file system, I like to use a directory structure comparable directory comparable to how Macros are organized for my Stored Processes, e.g.,

• /Projects/ProjectA/storedProcesses
  
• /Projects/ProjectB/storedProcesses
  
• . . .
  
• /Projects/Tools/storedProcesses
  

So, I would stored my runMacro stored process as /Projects/Tools/storedProcesses/runMacro.sas.

Given that the folder hiearchy in the SAS Metadata can be different, there are lots of options for packaging the runMacro Stored Process. Two examples (and there are lots of variations) are described below.

A single Stored Process

This option has the advantage that it can be used to run any macro in any of my project (or application) specific autocall libraries. So the location/name of the Stored Process in SMC might be:

• /Projects/Tools/runMacro

with Project as a required parameter (perhaps with a default that pointed to the Tools autocall macro location).

This has the advantage that one can easily run any macro merely by providing a value for Project in the hyperlink or HTML form.

Project Specific Stored Processes

Recall that the location in the SAS Metadata is completely independent of the physical location of the code. So we can have multiple instances of a runMacro Stored Process defined in SMC. Specifically, we can create

• /Projects/ProjectA/runMacro
  
• /Projects/ProjectB/runMacro
  

Note that

• there is no need for a folder hierarchy that specifies that these are Stored Processes
• the file extension of sas is not used as that is an artifact of the physical implementation.
• both Stored Processes can point to the sas program

Using this option, you would define Project as a parameter that is not viewable or editable. The instance in the ProjectA folder would have ProjectA specified as the default value and the instance in the ProjectB folder would have ProjectB specified as the default value.

Such an approach allows you to have completely separate macro libraries for the two projects (but with shared macros still available). As a result you can use the security features availble in SMC to specify what users/groups are allowed to run the ProjectA or the ProjectB macros.

Where, oh where, do my Macros live?

My last post provided a simple SAS program that could be packaged as a Stored Process and that could be used to run any macro. Lets generalize that program a bit - using macro variables.

But first lets talk a bit about a sample folder hierarchy for storing project code. I like to have a parent directory for projects/applications. As a consultant, typically I deal with projects and so I will have a structure like:

• /Projects/ProjectA
  
• /Projects/ProjectB
  
• . . .
  
• /Projects/Tools
  

where I like to use Tools for generic facilities that are not project or application specific (some people might want to refer to such things as Shared).

One simple way of pointing to the macro library is to include a statement like:

options sasautos=("/Projects/&Project/Macros"
                  "/Projects/Tools/Macros"
                  sasautos);

where the macro variable Project is defined as a parameter to the runMacro Stored Process. This code concatenates my Project specific autocall macro library, with my generic Tools macro and then the SAS supplied autocall macros.

So the complete Stored Process source is:

options sasautos=("/Projects/&Project/Macros"
                  "/Projects/Tools/Macros"
                  sasautos);
*ProcessBody;
%stpBegin;
%put NOTE: Execute macro &macroToRun..;
%&macroToRun
%stpEnd;

In my next post, I will address some packaging options so that I can reuse this same source for multiple projects.

And, BTW, did you notice I used the forward slash (/) in my file paths? That is because it works on both Unix and Windows, thus allowing the code to be migrated across environments more easily.

A simple Stored Process to run any Macro

OK. OK. OK. I know I have been negligent is posting the details about this. But I am still trying to wrap my head around doing things in tiny bits and pieces. I promise I will do better from now on ;-)!

In my last post I talked about creating a simple stored process that could be used to run any macro. So, for example, if you had lots of reports and wanted to make them avaiable from the Stored Process Server you need not create a distinct stored process for each one.

Create a stored process in an appropriate folder (more on this in later posts) and define the following three parameters:

1. macroToRun: the name of the macro that is to be run. Make this a required parameter.
   
2. _odsDest: the ODS Destination (e.g., HTML, PDF, RTF, and so on). Make this a required parameter and give it a default value.
   
3. _odsStyle: the ODS Style to use. This is an optional parameter as SAS will use a default style if not specified.
   

Now, create a sas program as the source for your stored process. Here is a simple version to get you started:

*ProcessBody;
%stpBegin;
%put NOTE: Execute macro &macroToRun..;
%&macroToRun
%stpEnd;

When this stored process is executed any values provided in the HTML form or hyperlink are available to the stored process and, thus, to the macro code. So presuming you specify myReport as the value of macroToRun, the SAS wordscanner will parse

%&macroToRun

and resolve it to:

%myReport

which will run your report macro assuming the path where it is located is defined to the Stored Process Server (and that will be the subject of an upcoming post).