BookmarkSubscribeRSS Feed
Jay
Calcite | Level 5 Jay
Calcite | Level 5
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?
2 REPLIES 2
WarrenR_SAS
SAS Employee
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
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 2 replies
  • 919 views
  • 0 likes
  • 3 in conversation