topic STP and multiple-value prompt PROC SQL in Developers

STP and multiple-value prompt PROC SQL

metalray — Wed, 17 Oct 2012 15:27:16 GMT

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.

Re: STP and multiple-value prompt PROC SQL

FriedEgg — Wed, 17 Oct 2012 16:01:24 GMT

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

Re: STP and multiple-value prompt PROC SQL

DavidRice — Wed, 17 Oct 2012 16:16:20 GMT

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 &quotedList eq yes %then

%let &listName. = %quote(%'&&&promptVar.%');

%else

%let &listName. = &&&promptVar.;

%else

%if &quotedList eq yes %then

%let &listName. = &&&listName., %quote(%'&&&promptVar&i.%');

%else

%let &listName. = &&&listName., &&&promptVar&i.;

%end;

%let &listName. = %unquote(&&&listName.);

%mend promptList;

Re: STP and multiple-value prompt PROC SQL

metalray — Thu, 18 Oct 2012 07:31:28 GMT

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;

Re: STP and multiple-value prompt PROC SQL

DavidRice — Thu, 18 Oct 2012 14:03:32 GMT

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

Re: STP and multiple-value prompt PROC SQL

acfarrer — Thu, 18 Oct 2012 21:56:15 GMT

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 ;

Re: STP and multiple-value prompt PROC SQL

Quentin — Mon, 22 Oct 2012 13:49:52 GMT

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 &quotedList eq yes %then
        %let list = %quote(%'&&&promptVar.%');
      %else
        %let list = &&&promptVar.;
    %else

      %if &quotedList 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.