Your SAS programs, embedded in web apps and elsewhere

STP and multiple-value prompt PROC SQL

Accepted Solution Solved
Reply
Regular Contributor
Posts: 207
Accepted Solution

STP and multiple-value prompt PROC SQL

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.


Accepted Solutions
Solution
‎10-18-2012 10:03 AM
Occasional Contributor
Posts: 5

Re: STP and multiple-value prompt PROC SQL

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,301

Re: STP and multiple-value prompt PROC SQL

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

    Regular Contributor
    Posts: 207

    Re: STP and multiple-value prompt PROC SQL


    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)Smiley SadColumn).
          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;

    Solution
    ‎10-18-2012 10:03 AM
    Occasional Contributor
    Posts: 5

    Re: STP and multiple-value prompt PROC SQL

    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

    Contributor
    Posts: 27

    Re: STP and multiple-value prompt PROC SQL

    Posted in reply to drice0504

    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 ;

    PROC Star
    Posts: 1,322

    Re: STP and multiple-value prompt PROC SQL

    Posted in reply to drice0504

    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.

    Occasional Contributor
    Posts: 5

    Re: STP and multiple-value prompt PROC SQL

    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;

    🔒 This topic is solved and locked.

    Need further help from the community? Please ask a new question.

    Discussion stats
    • 6 replies
    • 3051 views
    • 6 likes
    • 5 in conversation