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.