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 » Coding & Data, Enterprise Guide, Stored Processes

Taming SAS Stored Process Selection List Prompts

Submitted by on 2013-08-07 – 1:25 PM One Comment

Selection List

I like most things SAS.  But there are of course some features that I wish were implemented differently.  One of those is selection list prompts.  

The good news is, even when SAS implements something in a way that I find, well let’s say, surprising, there is often a way to use some other part of SAS to adjust the situation.  

For me, that often means writing a macro….



Selection List Prompts in a SAS Stored Process

Suppose you are developing a SAS stored process that will allow a user to print, what else, SASHELP.CLASS, and you want to add a prompt that will allow a user to specify which records are printed.  

You have a prompt NameList, which looks like:

sas stored process selection lists

And the source code of the stored process is something like:

proc print data=sashelp.class ;
* WHERE statement to select students goes here! ;
run ;

In order to write a WHERE statement that will work consistently, you need to understand what macro variables are created when the stored process runs.  And the disappointing news is….

Yikes! The Macro Variables Created Depend On the Number Of Items Selected

If a user selects one name (Alfred) the following global macro variables are created:

_Macro Variable_     _Value_ 
&NameList_Count 1
&NameList Alfred

If a user selects two names (Alfred and Barbara), the following macro variables are created:

_Macro Variable_     _Value_ 
&NameList_Count 2
&NameList0 2
&NameList  Alfred
&NameList1  Alfred
&NameList2 Barbara

If a user does not select any names, the only macro variable created is:

_Macro Variable_     _Value_
&NameList_Count      0 

Notice that different macro variables are created, depending on the number of items a user selects.   That can be a hassle to code around! NameList_Count is the number of items selected.  It is always created. NameList0 is also the number of items selected, but it is only created if two or more items are selected.  NameList is the first item of the list.  It is only created if at least one item is selected.  NameLIst1 is also the first item of the list.  But NameList1 is only created if two or more items are selected.  I’m tired just from writing that.  And I’m certainly not going to remember all of those rules.

I Want A List!

How would I want to receive the values selected by the user?  I can imagine two alternatives.

Some folks are fans of “macro arrays”.  In the three scenarios described above, they might want:

User selects Alfred:              &NameListCount=1 &NameList1=Alfred 
User selects Alfred and Barbara: &NameListCount=2 &NameList1=Alfred &NameList2=Barbara
User selects nothing: &NameListCount=0

Personally, I like having a list stored in a single macro variable.  So I would like:

User selects Alfred:              &NameList=Alfred 
User selects Alfred and Barbara: &NameList=Alfred Barbara
User selects nothing: &NameListCount= /*null*/

Maybe I would also like to be able specify the delimiter for the list (I typically use  a space, but if an item might have a space in it, I use a pipe as the delimiter).


Since SAS doesn’t create what I would want (a single macro variable with this list of items selected), I wrote a macro function which will build it for me from the mix of macro variables that are created by default.  The macro is:

%macro ConcatenateSelectionList (prompt= , dlm=%str( ) ) ; 
%local i return ;
%if &&&prompt._Count ge 2 %then %do i = 1 %to &&&prompt._Count ;
%let return=&return&dlm&&&prompt&i ;
%end ;
%else %do ;
%let return=&&&prompt ;
%end ;


%mend ConcatenateSelectionList ;

As with many of the macros in my library, this is not something I thought up on my own. It’s an adaptation of a macro, %numGen(), I read in a helpful paper on stored processes by Joe Flynn at SAS.

 It’s  a macro function, and it returns the list I want.  I use it in the stored process source code like:

%global NameList ; 
%let NameList = %ConcatenateSelectionList(prompt=NameList) ;
proc print data=sahelp.class ;
where findw("&NameList",trim(name) ) ;
run ;

%ConcatenateSelectionList() returns a list of the selected names.  Once I have that list, I can use it anywhere I want in my code.  As a macro programmer, I like working with lists (lists of datasets, lists of variables, etc.).

One More Utility Macro: %SepList  

Notice that above I used the FINDW function on the WHERE statement, rather than the IN operator.  I did that, because to use the IN operator, you need to add double quotes around each item in the list:

  where name IN ("Alfred","Barbara") ;

But here’s the good news.   After calling %ConcatenateSelectionList, I have a list of names.  Adding quotes to each item is just list manipulation, which is a common task in macro programming.  And here’s the better news.  Richard DeVenezia has already published a nifty utility macro which will do the work.    His macro, %SepList, takes a list of items as input, and returns a list of items, with options to specify a delimiter, quotation marks around each item, and much more.  With the help of this handy macro, I can code the stored process as:

%global NameList ; 
%let NameList = %ConcatenateSelectionList(prompt=NameList) ;
proc print data=sashelp.class ;
where name IN ( %unquote(%SepList(&NameList,nest=QQ)) ) ;
run ;

Pain Point??? Utility Macros to the Rescue

The way selection list prompts works was a small pain point for me.  %ConcatenateSelectionList() is a little workaround.  It makes my (coding) life easier.  When you notice pain points that pop up again and again in your coding process, I’d encourage you to think about whether a utility macro could be part of a solution.  

And if you like the way that SAS handles selection list prompts, or have an explanation (defense?) of the odd family of macro variables that are created from a selection list prompt, please leave your thoughts in the comments below.

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.

Quentin McMullen

Quentin McMullen has been programming in SAS for 15 years, and for the past year has been working on SAS BI projects. He has presented at national and regional SAS user group conferences, and can often be found corresponding with colleagues on SAS-L.
Spread the love

Tags: ,

One Comment »

  • I agree that the api for how multiple name/value pairs is handled is not ideal. This api dates back to the 90s and the SAS/IntrNet Application Dispatcher. It was chosen over the alternatives you listed for a number of reasons. Some of them are cited here:

    The primary problem faced back then (and, yes, I was on the design team at SAS) was how to deal with how name/value pairs are handled in URLs and HTML forms. The same name can be used for multiple values. This is true for any HTML element, not just select tags. It is most common for checkboxes. But it can occur for any name/value pair. When a name/value pairs as passed to the mid-tier by the browser (either the Stored Process Web app or the IntrNet broker), it has no way of knowing whether a name that has just one value could have had multiple values. Likewise, it has no way of knowing that a missing name (because none of the checkboxes were checked) needs to be added.

    The reason this api was selected was because
    – it was the lesser of all evils
    – all the conditions could be handled via a utility macro
    – a single solution that worked for any HTML element was needed

    The above link includes such a macro and it pretty much handles all the variations, making sure that the numbered macro vars exist. The stored process merely has to know what names could have multiple values.

    I first described this macro in my book on the SAS/IntrNet Application Dispatcher. A variation of it has also been included in the the various SAS courses on IntrNet and the Stored Process Server (at least they used to be).