BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metalray
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
DavidRice
Fluorite | Level 6

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

6 REPLIES 6
FriedEgg
SAS Employee

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

    metalray
    Calcite | Level 5


    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;

    DavidRice
    Fluorite | Level 6

    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

    acfarrer
    Quartz | Level 8

    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 ;

    Quentin
    Super User

    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.

    BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
    DavidRice
    Fluorite | Level 6

    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;

    sas-innovate-2024.png

    Don't miss out on SAS Innovate - Register now for the FREE Livestream!

    Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

     

    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
    • 6 replies
    • 5874 views
    • 6 likes
    • 5 in conversation