SAS Enterprise Guide multiple values in Prompt

[DLehwidi]

Good day, I am having a problem with a multiple values prompt, no matter how many names I put in the prompt, it only returns the first name on the list, in this case it returns records for Hunter and the rest it doesn't.

below is the code i am using 

I tried where clause with 'IN operation, the = operation and now the LIKE operation' but the results are the same.

Thank you so much for the assistance.

Kind regards

data Resident_Name_Search;

    set FNS_DETAIL_O;

    where valuedate between "&value_date_min."d and "&value_date_max."d

      and (

        upcase(R_SURNAME_OR_LEGALENTITYNAME) like upcase("&resident_customer") or

        upcase(R_NAME_OR_TRADINGNAME) like upcase("&resident_customer") or

        upcase(INDIVIDUAL3PARTYSURNAME) like upcase("&resident_customer") or

        upcase(INDIVIDUAL3PARTYNAME) like upcase("&resident_customer") 

);

run;

[andreas_lds]

EG creates a macro variable for each value in the list. With a variable named "cust_name" and two values, %put _user_; returns:

GLOBAL CUST_NAME hunter
GLOBAL CUST_NAME0 2
GLOBAL CUST_NAME1 hunter
GLOBAL CUST_NAME2 peter
GLOBAL CUST_NAME_COUNT 2

ADD-ON:

To make things a bit more interesting, sas the variables cust_nameX are not created, if only one value is provided, in this case you get cust_name and cust_name_count, only.

[DLehwidi]

I have about million customers in the list, a client requested a name search, so it can be any name in the world , Hunter and Peter was just for the sake of concept

[andreas_lds]

---

@DLehwidi wrote:
I have about million customers in the list, a client requested a name search, so it can be any name in the world , Hunter and Peter was just for the sake of concept

---

I just tried to point you in the right direction, with less success than expected, it seems.
The variable resident_customer will always contain the first name entered, only.

If more than one name is entered, "hunter" and "peter" in your example, sas will create additional macro-variables, one for each value entered. If the client needs to be able to provide multiple names with each execution of the program, you have to process the variables created by the prompting-framework. I will try to post some useful code later.

[Tom]

It should not matter how large the database being searched is (other than performance).  But if you meant that you need to allow the user to enter a list of a million customer names then do not use that type of prompt.   Instead have them upload a DATASET.

The point is that your code it not even trying to use all of the values the user entered.   Your code is only using the first value.  The first value will be placed in the macro variable without a numeric suffix and also into the macro variable with a suffix of 1.  The second value will only be placed in the macro variable with the suffix of 2.

So what code do you want to generate when the user enters more than one name?

Perhaps something like using FINDW() to check if any of the names appears in any of the variables of interest.

and (findw(catx('|',R_SURNAME_OR_LEGALENTITYNAME,R_NAME_OR_TRADINGNAME,INDIVIDUAL3PARTYSURNAME,INDIVIDUAL3PARTYNAME)
          ,"&resident_customer1",'|','i')
 or findw(catx('|',R_SURNAME_OR_LEGALENTITYNAME,R_NAME_OR_TRADINGNAME,INDIVIDUAL3PARTYSURNAME,INDIVIDUAL3PARTYNAME)
          ,"&resident_customer2",'|','i')
   ...
    )
;

 You can use a %DO loop to generate that code.

... and (
%do i=1 %to &resident_customer_count ;
  %if &i=1 %then %let resident_customer1=&resident_customer;
  %else or ;
findw(catx('|',R_SURNAME_OR_LEGALENTITYNAME,R_NAME_OR_TRADINGNAME,INDIVIDUAL3PARTYSURNAME,INDIVIDUAL3PARTYNAME)
          ,"&&resident_customer&i",'|','i')
%end;
)

[andreas_lds]

Using the idea provided by @Tom the following code creates a dataset using the names entered in the prompt:

data work.selected_customer;
    length
        cust_name $ 50
    ;

    %if &resident_customer_count = 1 %then %do;
        cust_name = "&resident_customer";
        output;
    %end;
    %else %do;
        do i = 1 to &resident_customer_count;
            cust_name = symget(cats("resident_customer", i));
            output;
        end;
    %end;

    drop i;
run;

And a small demonstration of the usage together with sashelp.class, because i am to lazy to make up a dataset with four name-variables:

proc sql;
    select c.*
        from sashelp.class as c, work.selected_customer as s
            where find(c.Name, s.cust_Name, 'it')
    ;
quit;

[Quentin]

As others have explained, when you use a multi-valued prompt, the values are stored in multiple macro variables.

This paper by Joe Flynn has a section explaining an approach:

http://support.sas.com/resources/papers/proceedings11/050-2011.pdf

Based on that approach, I created a function-style macro which returns a delimited list of the selected items:

%macro ConcatenateSelectionList
  (prompt
  ,dlm=%str( )
  );

/*==============================================================================
Macro:         ConcatenateSelectionList

Abstract:      Macro function that returns a delimited list of items chosen in
               a check all that apply stored process prompt.

Description:   Stored processes pass CHECK All That Apply parameters in an odd fashion.
               If parameter is named PROMPT then
                 &PROMPT_Count always exists, holds number of options selected (0-n)
                 &PROMPT1 - &PROMPTN are created if count>=2, if count=1 or 0, they not created
                 &PROMPT is created  if count >=1, if count=0 it is not created.

Usage:         %let sitelist=%ConcatenateSelectionList(site)
================================================================================*/

%local i return ;

%if &&&prompt._Count >= 2 %then %do i = 1 %to &&&prompt._Count ;
  %if &i=1 %then %let return=&&&prompt&i ; 
  %else          %let return=&return&dlm&&&prompt&i ;   
%end ;
%else %if &&&prompt._Count = 1 %then %do ;
  %let return=&&&prompt ;
%end ;
%else %if &&&prompt._Count = 0 %then %do ;
  %let return= ; %*null ;
%end ;
%else %put ER%str()ROR: USER &prompt._Count=&&&prompt._Count ;

&return

%mend ConcatenateSelectionList;