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? */
CREATE TABLE WORK.QURY4672 AS SELECT WORKORDER.WONUM FORMAT=$10.,
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?
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. */
/* 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;
/* 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 */
%if %superq(eqnum0) ne /* If counter is present */
%then %do i=2 %to %superq(eqnum0);
/* Append 2nd, 3rd value, etc. */
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.