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;

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.