Allowing for Multiple Selections in an sql - stored process?

[Jay]

I started with a simple EG query to allow for multiple and unknown number of selections of the eqnum:
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
%LET eqnum=%NRSTR("10-1380"),%NRSTR("10-1428"),%NRSTR("10-1429"); /* eqnum? */
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
PROC SQL;
CREATE TABLE WORK.QURY4672 AS SELECT WORKORDER.WONUM FORMAT=$10.,
WORKORDER.EQNUM FORMAT=$8.,
WORKORDER.more_stuff
FROM MAXCOR.WORKORDER AS WORKORDER
WHERE ( WORKORDER.EQNUM IN ( &eqnum );
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
What does the code look like to allow for multiple selections in a stored process?

[WarrenR_SAS]

Multiple values for a single stored process parameter are provided as individual values with a numeric suffix attached to the parameter name. Here's a short code sample that can put the values together in the desired fashion, e.g., enclosed in quotes with comma separators.


/* EG generates this %LET statement. */
%LET eqnum=%NRSTR("10-1380"),%NRSTR("10-1428"),%NRSTR("10-1429");

/* SP parameters generate equivalent of these %LET statements */
/* when 2 or more values are supplied. */
%let eqnum1=10-1380; /* First selection */
%let eqnum2=10-1428; /* Second selection */
%let eqnum3=10-1429; /* Third selection */
%let eqnum0=3; /* Selection count */
%let eqnum=10-1380; /* Same as first selection */

/* Always anticipate first selection and number of selections. */
%global eqnum eqnum0;

%macro CombineEqNum;
/* Assumes SP parameter requires at least one selection. */
/* Additional logic required if zero selections is valid. */
%let eqnum="%superq(eqnum)"; /* Put quotes around first value */
%local i;
%if %superq(eqnum0) ne /* If counter is present */
%then %do i=2 %to %superq(eqnum0);
/* Append 2nd, 3rd value, etc. */
%let eqnum=&eqnum,"%superq(eqnum&i)";
%end;
%mend CombineEqNum;
%CombineEqNum

There is a more detailed explanation of the multiple value creation in the Stored Process Developer's Guide at http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/input.html

There is a reminder that only the stored process server currently supports multiple values for a parameter. The workspace server only supports one value.

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