SAS Prompts: When do you use a Variable Prompt?
If you have spent anytime building SAS prompts for a stored process, then you might have noticed the prompts near the bottom of the list and wondered how anyone would use those or even need them. In a previous post, I showed you how to use the data library prompts. In this post, I’m going to show you an example of the Variable prompt (the one at the very bottom of the list from SAS Enterprise Guide!)
How many ways can you count something?
Without a doubt, I try to make my stored processes as one-size-fits-all. This creates less coding for me and users seem to adapt to an all-purpose stored process. When working across departments, I have noticed that each department measures the same things but use different variables. For this discussion, I’m using a dataset called Current_Orders that contains the shipping data for customer orders. The Current_Orders dataset has three date fields: OrderDate, RequiredDate, and ShippedDate, as shown in the the following figure.
This is where the fun begins. It’s easy to imagine the following scenario: Dept A, Shipping, wants to know which orders are required to ship tomorrow, while Dept B, Finance wants to know what has shipped and can be counted toward revenue, and maybe Dept C, Sales is interested in how many orders are in dispute. Rather than build three different stored processes for this dataset – let us just build one stored process and let the report consumer choose the date value they want and the order status (Shipped, Disputed).
This stored process (code is below) has two prompts: Status and variable. Status contains the order shipment status; it can be Shipped, On hold, or Disputed. The X Axis variable can be any of the three date fields from the Orders_Current dataset (above).
Using the Variable Prompt
Setting Up the Stored Process Code
There’s really nothing special about the stored process code – it just converts the date variable to a single month. Using the INTNX function, the date variable is changed from a specific day of the month to the first day of the month. Then the resulting Month variable can be easily grouped. The only requirement is that the &datevar. is an actual date variable. As you can see in the dataset above – the three date variables are there.
Building the Prompt
To build the datavar prompt, start a prompt called datevar and then do the following:
- From the Prompt Type and Values pane, select Variable from Prompt type drop-down list.
- Select the variable from the Variable type box. I selected Date.
- Click the Load Values button to populate the Value list box. SAS brought the three date fields over.
Note: I also added ShippedDate as the default variable.
Stored Process Code
When you register this prompt – make sure you do not allow the %STPBEGIN/%STPEND macros to be turned on. Angela discussed that in this topic.
libname toys meta library="Toys" metaout=data; /*===============Get dataset for the chart=====================*/ %macro doit; proc sql; create table chart_me as select intnx('month', &datevar., 0) as Month format=monyy. ,status label="Status" ,ordernumber as COUNT from toys.orders_current where status in (%if &STATUSPROMPT_COUNT. = 1 %then %do; "&STATUSPROMPT." %end; %else %do i=1 %to &STATUSPROMPT_COUNT.; "&&STATUSPROMPT&i." %end;) order by 1, 2; quit; %mend; %doit /* =========== BUILD CHART ======================*/ %stpbegin; Legend1 FRAME CBORDER=WHITE CFRAME=WHITE LABEL=(FONT="Tahoma, Arial" HEIGHT=10pt JUSTIFY=LEFT ); Axis1 STYLE=1 WIDTH=1 MINOR=NONE LABEL=(ANGLE=90 "Order Count") ; Axis2 STYLE=1 WIDTH=1; TITLE "Order Status by &datevar. "; PROC GCHART DATA=chart_me; VBAR Month / SUBGROUP=status CLIPREF NOFRAME TYPE=FREQ DISCRETE LEGEND=LEGEND1 COUTLINE=BLACK RAXIS=AXIS1 MAXIS=AXIS2 ; RUN; QUIT; %stpend;
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!
Latest posts by Tricia Aanderud (see all)
- SAS Add-In for MS Office: Abe Lincoln Sends Glee in a Snowstorm - February 18, 2014
- #SASGF14: You’re Invited to a TweetUp! - February 10, 2014
- SAS Stored Process Dealing with Disappearing Results - January 31, 2014
- More SAS Stored Process Sleight of Hand Tricks - January 29, 2014
- Little Known Secrets from SAS Stored Process Magicians - January 26, 2014