SAS Stored Process: Shake, Rattle, and a lil’ DelGobbo Style
One of the great things about attending user conferences such as WUSS is that you get to meet all kinds of fun and interesting people. Vince DelGobbo is a witty, smart, and clever SAS developer, who also happens to work at SAS Institute. Vince works with the SAS Stored Process development – so you know I love him!
In a recent blog post, I discussed how to use highlighting to make your output easier to use. My last comment was - “tell me how you use it”. Vince did one better – he showed me how to kick it up a notch.
Output to Multiple Destinations ala DelGobbo
This text is from the email message Vince sent to me:
The beauty of using ODS to do the “traffic lighting” is that it works for the HTML, RTF, PDF and ExcelXP destinations. I took your code and massaged it a bit to work with all of these destinations (see below). Also if the user runs from Enterprise Guide or the Add-In for MS Office – this stored process can handle it.
The largest change was to add a DATA step to deal with the Content-Type headers and a couple of special options for ExcelXP (code block shown with #2). However, the code also deals with users who might run the code from SAS EG or SAS AMO. The ExcelXP tagset does not work so well with those clients, so we can just intercept and handle the output in a different way (code block shown with #1).
Note: This technique of setting MIME headers is used in the “Multiple Output Formats” stored process sample that [SAS Institute] ships.
My prompts looks like this (see below), with the required prompts shown with an asterisk. The “Country” and “Region” prompts are dynamic and are sourced by the PRDSALE table (“Region” depends on “Country” though I think in this case “Region” could be static).
I talk about this to varying degrees, mostly from the Excel point of view, in these papers:
- Traffic Lighting Your Multi-Sheet Microsoft Excel Workbooks the Easy Way with SAS®
- Creating Stylish Multi-Sheet Microsoft Excel Workbooks the Easy Way with SAS®
He’s right – both of these papers discuss some cool ways to gain complete control of the look and feel of the spreadsheet style. I appreciate how Vince takes a deep dive on these topics and really instructs you.
Let Me Try!
So using the code Vince provided in the figure above – I updated my stored process from this post. One thing I had to do was create my own Output Type prompt. Based on Vince’s code – I made four output values available and set HTML as the default.
Here’s my result in the SAS Stored Process Web Application window. After selecting MS Excel – Web Only, the stored process prompted me to open or save the result. When I clicked Open, MS Excel started and opened the spreadsheet. Boom! Using .XML as the file extension worked really well – MS Excel 2010 did not generate a warning about the file extension and ensuring it was a trusted source. However, you can also use the trusted .XLS file extension if you prefer – but you will get a Nag message that makes you feel like a straight-up gangster.
Learn More about SAS Stored Processes and Prompts
You can learn more tips and tricks for creating, debugging, and using SAS stored processes in the 50 Keys to Learning SAS Stored Processes book. It's a complete guide to SAS stored processes! Download a sample chapter or view the table of contents.
Tip! Now 20% off CreateSpace and Amazon!