Taming SAS Stored Process Selection List Prompts

This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post.

Author: Quentin McMullen

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:

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

%ConcatenateSelectionList

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 ; 

  &return
 
%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! Download a sample chapter or view the table of contents.