BookmarkSubscribeRSS Feed
Not applicable
A while back I came across a general purpose macro for filtering multiple selections in stored process. I was told that the macro came from a SAS internal shared library and was provided to customers "as is" (without support).

It is called the %WhereParam macro. The information I got with it also said that it was "... an experimental feature under development. Some functionality is not fully documented or tested. This documentation applies to SAS 9.1, 9.1.2 and 9.1.3."

The macro worked great when I was running EG3, but since upgrading to EG4 it only works when I select multiple parameters and if I select only a single parameter I get the following errors:
ERROR: %EVAL function has no expression to evaluate, or %IF statement has no condition.
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro WHEREPARAM will stop executing.

I'm not good enough at macros to debug and am wondering if there is anyone out there that could help.

I'm aware of the method identified in this post, but the %whereparam macro was cleaner code and easier to apply to a stored process.

NOTE: I initially added this to an existing post, but have not received any replies so thought that maybe I should have made it a new post.
The macro logic that you use will be different, dependent on whether the user has made two or more selections, one selection or no selections.

I do not know exactly how the WHEREPARAM macro works, nor whether it will continue into the future. However, here's a synopsis of the way multiple selection parameters are created and how you must handle them in your code (without regard to the WHEREPARAM macro). Let's say you have an input parameter called "WOMBAT". Normally, if this was a single value parameter, your value for this parameter would be accessible in your stored process code under the reference "&WOMBAT".

However, if you allow multiple selections, then this is how values get returned. Note that these are 3 different scenarios. Sorry for the long post.

** Scenario 1: User Makes 2 or more selections for the "WOMBAT" parameter:
&WOMBAT0 will contain the number of selection (so if they selected 3, then &WOMBAT0 = 3)
&WOMBAT1-&WOMBAT3 will contain the selections.
&WOMBAT will contain ONE of their choices (but it will have a value -- you just don't need it).

Code for Scenario 1 to loop through the list of values:
%do i = 1 to &wombat0;
. . .


** Scenario 2: User Makes ONLY 1 selection for the "WOMBAT" parameter:
&WOMBAT0 is NOT created -- so your do loop will fail unless you manually adjust/create the macro variable.
&WOMBAT1 is NOT created -- so again, you must manually adjust/create the macro variable.
&WOMBAT WILL hold the single value that they selected. But, if you want to use your do loop, then you must create &WOMBAT1 and give it the value that is held in &WOMBAT.

Code for Scenario 2 to execute the loop 1 time:
%if &wombat0= %then %do;
%let &wombat0=1;
%let &wombat1=&wombat;
%do i = 1 to &wombat0;
. . . %do loop will execute 1 time

** Scenario 3: User Makes NO selections for the "WOMBAT" parameter:
&WOMBAT0 is NOT created -- so your do loop will fail unless you manually adjust/create the macro variable.
&WOMBAT1 is NOT created -- so again, you must manually adjust/create the macro variable.
&WOMBAT is NOT created -- so again, you must manually code for this situation.

Code for Scenario 3 to execute the loop 0 times and possibly do something else instead:
%if &wombat= %then %do;
%let &wombat0=0;
%let &wombat=;
%let &wombat1=;
%do i = 1 to &wombat0;
. . . %do loop will NOT execute
*** maybe issue an error message to the log or something else here;

Anytime you use multiple selections, you must utilize some kind of code like this -- in order to make sure that your stored process code executes correctly. It would be easier if you made the parameter required, so you did not have to code for the 0 selection situation. But, you would still need to code for the 1 value selection situation.

I hope this explanation makes it easier for you to understand the macro processing that needs to be performed, whether you use WHEREPARAM or not. The code snippets above would need to be part of your stored process code -- you might choose to have a series of nested %IF/%ELSE statements and only cycle through the loop (%DO) once or you might package the statements into a callable macro program. But that is a whole 'nother topic. For more information about Macro %DO loops and using the %IF and the %LET statements, refer to the SAS Macro facility documentation.




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2 in conversation