BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » Stored Processes

SAS Stored Process: Shake, Rattle, and a lil’ DelGobbo Style

Submitted by on 2012-11-07 – 7:20 AM 2 Comments

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!

The following two tabs change content below.

Tricia Aanderud

SAS Consultant at Zencos
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has over 10 years of SAS programming experience. Contact her for assistance with your next project.

Tags: , ,