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:

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. Check Amazon for best pricing and quick shipping!

2 thoughts on “SAS Stored Process: Shake, Rattle, and a lil’ DelGobbo Style

  1. I agree that Vince is a real wizard with this. He is one of my go-to folks when I want to brainstorm on ideas for dressing up stored process output.

    A few years ago he and I collaborated on my runMacro stored process (see which uses a number of the techniques described here. The sasCommunity page has links to a bunch of my blog entries on the topic.

    And in this blog entry,, there is an example (for web based stored processes) of allowing the user to see an HTML based report and then download a PDF or Word version without having to rerun the code – again thanks to some ODS and stored process magic. And Excel output, could be just as easily added.

Comments are closed.