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 Prompts: When do you use a Variable Prompt?

Submitted by on 2011-12-26 – 8:00 AM

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!)

 sas prompts choices

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. 

sas prompts variable 1

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).

sas prompts stored process example

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.

sas prompts sas stored process example

Building the Prompt

To build the datavar prompt, start a  prompt called datevar and then do the following:

  1. From the Prompt Type and Values pane, select Variable from Prompt type drop-down list.
  2. Select the variable  from the Variable type box. I selected Date.
  3. 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.

sas prompts variable 5


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
		,status label="Status"
		,ordernumber as COUNT
	from toys.orders_current
	where status in
	(%if &STATUSPROMPT_COUNT. = 1 %then %do;
		   %else %do i=1 %to &STATUSPROMPT_COUNT.;
	order by 1, 2;
/* =========== BUILD CHART ======================*/
	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. ";
	VBAR Month
 /	SUBGROUP=status

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

Director of Data Visualization at Zencos Consulting
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 been using SAS since 2001. Contact her for assistance with your next project.
Spread the love

Tags: ,