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).