Multiple selections in a stored process

[deleted_user]

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.

[Cynthia_sas]

Hi:
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:
[pre]
%do i = 1 to &wombat0;
. . .
%end;

[/pre]

** 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:
[pre]
%if &wombat0= %then %do;
%let &wombat0=1;
%let &wombat1=&wombat;
%do i = 1 to &wombat0;
. . . %do loop will execute 1 time
%end;
%end;
[/pre]

** 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:
[pre]
%if &wombat= %then %do;
%let &wombat0=0;
%let &wombat=;
%let &wombat1=;
%do i = 1 to &wombat0;
. . . %do loop will NOT execute
%end;
*** maybe issue an error message to the log or something else here;
%end;
[/pre]

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.

cynthia