Jurassic SAS® in the BI/EBI World

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

2011-09-12T12:01:00.017-04:00

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:

There is no 32K limit on the text produced by a single input line in a SAS Server Page.
A much broader range of SAS statements can now be used, including macro definitions and %include statements.
SAS code can be embedded and executed from within a SAS Server Page.
The %sysfunc macro can invoke the SCL functions that access data.
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:

HTML Reports and UIs.
Word documents (as RTF files).
XML documents
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

2011-08-16T08:00:00.006-04:00

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:

Note that the formula uses the Excel R1C1 notation (and it works regardless of whether this reference style is enabled in Excel).

RC[-1] uses the value from the same row, one column to the left.
RC[-2] uses the value from the same row, two columns to the left.
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.

Since Excel chooses to do its own thing with formats, the format attribute is used to tell Excel how to format the value.
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!

2011-05-16T09:00:00.005-04:00

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

2011-04-11T08:00:00.002-04:00

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;

Generating Descriptive Please Wait Messages for Long Running Stored Processes

2010-07-12T08:00:00.003-04:00

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:

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

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

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

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.

2010-03-03T07:10:00.010-05:00

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

2010-02-18T05:00:00.000-05:00

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

2010-02-02T08:15:00.001-05:00

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

2010-01-26T09:30:00.002-05:00

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?

2010-01-21T09:30:00.000-05:00

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

2010-01-18T09:15:00.001-05:00

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:

pageheight
pagewidth
sort
frozen_rowheaders
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

2010-01-15T09:15:00.001-05:00

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?

2010-01-13T09:15:00.002-05:00

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

2010-01-11T16:41:00.002-05:00

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:

macroToRun: the name of the macro that is to be run. Make this a required parameter.
_odsDest: the ODS Destination (e.g., HTML, PDF, RTF, and so on). Make this a required parameter and give it a default value.
_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).

Dressing Up PROC REPORT

2009-12-06T21:35:00.002-05:00

or PRINT, or FREQ or TABULATE, or . . . . far be it from me to re-start SAS PROC reporting wars ;-).

So lets say you have lots of reporting programs that you would like to make available on the SAS Portal. Since you already have SAS code and data to produce all the reports, you don't want to have to recreate them all using, for example, Web Report Studio (WRS).

But yet you do need/want some of the cool features that you've seen WRS can do, e.g.,

frozen column headers as you scroll
sorting the table on any column
and so on, and so on. . . .

One solution to this is to use the SAS TableEditor tagset.

The following simple program (which is run as a Stored Process) which summarizes/groups the data demonstrates this. Use the above link to find out what the various TableEditor options in the ods statement do. And, of course, there are lots, lots more options that that do all sorts of kewl things.

ods tagsets.tableeditor file = _webout
    options(sort="yes" pageheight="300" pagewidth="490"
            background_color="white"
            frozen_headers="yes" frozen_rowheaders="yes");

proc report data = sashelp.shoes nowd
     style(column) = [font_size=9pt]
     style(header) = [font_size=9pt];
 columns Region Stores Sales Inventory Returns;
 define Region / group style(Header) = [tagattr="type='String'"];
 define _numeric_ / sum style(Header) = [tagattr="type='Numberx'"];
 footnote h=1 j=left 'Click on the Column Header to Sort the Rows in the Table';
run;
ods tagsets.tableeditor close;

Give it a test run on my server if you want to see this in action on the SAS Portal.

My next few posts will talk about how to package code like this using the macro facility in order to make it easy to run them as stored processes. I'll also talk about some other things you can do to make the code a bit glitzier.

Connecting Two Worlds

2009-12-02T20:45:00.003-05:00

Since its inception, SAS has always been used by different folks with a completely different perspective on what it is and how it is used (e.g., Statisticians/Data Analyts and users who just crank out reports). Having seen this countably infinite times, it frustrates me when I see people building applications using the suite of tools available in the BI/EBI platform and commenting that Gee, I wish I could do XXXX where what they want to do is readily available in the Base SAS toolset.

Sooooo, I finally decided to create a blog to provide examples and tips to point out the (too many to count) cases where marrying these two worlds brings a lot to the table.

This blog is directed to both

the traditional SAS users who want to learn how their skill-sets might apply in the brave, new BI world.
BI/EBI developers and users, who perhaps only know about cubes, Web Report Studio, and so on. I hope to make them aware of all the great things you can do with simple SAS programs (packaged perhaps as Stored Processes).

So let the blogging begin. I know there are plenty of topics that I can address. Just need to find the time and the motivation!