Hello Guys,
I have my STP and a prompt defined that allows to the user to select multiple values (by selecting the value, clicking the blue arrow to take it over to the selected-values box). Now in my PROC SQL I want to filter those values but dont quite now how to do it: The prompt value returend is of a single value and not off of those
in a list like I could use for SQL as follow
WHERE myname IS IN (&allpromptvalues)
I looked at the %_eg_WhereParam macro but that did not do a miracle either.
Has someone done that and can help me?
We use SAS 9.2
Thanks.
Hello metalray,
We would call the macro function prior to the PROC SQL and then use the generated "list" macro variable as you described. If the unformatted prompt values are numeric, we set the quotedList parameter to no; if the unformatted prompt values are character, we set the quotedList parameter to yes.
So, the code might look like this:
%promptList(
promptVar = userID,
quotedList = no,
listName = userList
)
proc sql;
...
where USER_ID in (&userList);
quit;
Hope this helps,
David
Let say you have a stored procedure, as you say, with one static list that allows multiple selections, and it is call PROMPT_1, inside the stored procedure when it execute it there will be several macro variables created containing the information you want:
PROMPT_1 will contain only the first, single value selected
PROMPT_1_COUNT will contain the count of items selected
PROMPT_10 the zero index will also contain the count of items selected
PROMPT_11-N the positive integer indexes will contain their respective values
*remove comments if using character values in prompt */
data _null_;
array prompt_1[&prompt_1_count] /*$*/;
do i=1 to dim(prompt_1);
prompt_1=/*quote(*/symget(cats(vname(prompt_1),i))/*)*/;
end;
call symputx('allpromptvalues',catx(',',of prompt_1
run;
Message was edited by: Matthew Kastin There was a syntax error in the array reference in the call symputx function
Hello drice0504,
I tried your macro but how do I use it in the sql query?
My idea was to use the macro variable that is the list and
put it in the code like this:
PROC sQL...
WHERE contractname IN (&LListOfContracts)
Thanks for your input.
@FriedEgg, thanks for that as well. I have a dynamic list.
If I try to put in my prompt name in your example I get the following error:
ERROR: Illegal reference to the array prompt_1.
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
1159:1
NOTE: The SAS System stopped processing this step because of errors.
data myNewlist;
array prompt_1[&contractsprompt_count] ;
do i=1 to dim(prompt_1);
prompt_1=symget(cats(vname(prompt_1),i));
end;
call symputx('allpromptvalues',catx(',',of prompt_1));
run;
Hello metalray,
We would call the macro function prior to the PROC SQL and then use the generated "list" macro variable as you described. If the unformatted prompt values are numeric, we set the quotedList parameter to no; if the unformatted prompt values are character, we set the quotedList parameter to yes.
So, the code might look like this:
%promptList(
promptVar = userID,
quotedList = no,
listName = userList
)
proc sql;
...
where USER_ID in (&userList);
quit;
Hope this helps,
David
This is old-school code that I have committed to memory because it is so useful. Give it a try:
proc sql noprint ;
select distinct quote(trim(make)) /* Double quotes */
into :charpromptvalues
separated by ','
from sashelp.cars ;
select distinct cylinders
into :numpromptvalues
separated by ','
from sashelp.cars
where cylinders gt 0 ;
quit ;
%put &charpromptvalues ;
%put &numpromptvalues ;
Another option would be to turn David's nifty macro into a function -style macro, by having it return a value at the end. Something like (untested):
%macro promptList( promptVar = /* specify the base name of the prompt to be made into a list */, quotedList = yes /* individual items in list will be quoted if yes */,
) ; %local i list; %* Build a list, of the prompt values selected. *; %* The &promptVar._COUNT macro variable exists for multi-select macro prompts, so *; %* loop through all the values selected - adding them to the list *; %do i = 1 %to &&&promptVar._COUNT.; %if &i. eq 1 %then %if "edList eq yes %then %let list = %quote(%'&&&promptVar.%'); %else %let list = &&&promptVar.; %else %if "edList eq yes %then %let list = &&&listName., %quote(%'&&&promptVar&i.%'); %else %let list = &&&listName., &&&promptVar&i.; %end; %let list = %unquote(&&&listName.); &list /*return value*/ %mend promptList;
Then you call it like:
proc sql; ... where USER_ID in (%promptList(userList)); quit;
--Q.
We call a macro to convert the enumerated prompt-generated macro variables into either a quoted or non-quoted list. The quoted list might be handy for your SQL query.
Here's the macro that we use (it could probably be more robust, but it works for us):
%macro promptList(
promptVar = /* specify the base name of the prompt to be made into a list */,
quotedList = yes /* individual items in list will be quoted if yes */,
listName = /* specify the name of the list to be created */
);
%* Build a list, named by the listName macro parameter of the prompt values selected. *;
%* The &promptVar._COUNT macro variable exists for multi-select macro prompts, so *;
%* loop through all the values selected - adding them to the list *;
%do i = 1 %to &&&promptVar._COUNT.;
%if &i. eq 1 %then
%if "edList eq yes %then
%let &listName. = %quote(%'&&&promptVar.%');
%else
%let &listName. = &&&promptVar.;
%else
%if "edList eq yes %then
%let &listName. = &&&listName., %quote(%'&&&promptVar&i.%');
%else
%let &listName. = &&&listName., &&&promptVar&i.;
%end;
%let &listName. = %unquote(&&&listName.);
%mend promptList;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.