concatenating prompt multiple selection within where clause in proc sql

[Lior2020]

Hi
I have a prompt which allows the user to select multiple items.
i'm trying to use these selections with in statement, but to use a macro for concatenating these selections, since i have many variables with prompts.

the macro below works fine (concatenating with delimiter), but the proc sql makes an error.
i would appreciate any help

here is the code:




%macro CatPrompt (prmpt);
%local i emit dlm1 dlm2;
%let emit=;
%let dlm1 = %str(%');
%let dlm2 = %str(,);
%let i = 1;

%if &&&prmpt._count>0 %then %do;
%let emit = &dlm1.&&&prmpt&i.&dlm1.;
%do i=2 %to &&&prmpt._count;
%let emit=&emit.&dlm2.&dlm1.&&&prmpt&i.&dlm1.;
%end;
%end;

&emit;
%mend CatPrompt;





PROC SQL;
CREATE TABLE WORK.Tbl2 AS
SELECT *
FROM WORK.Tbl1
WHERE (&Prompt_MultUsrSlct_count=0) OR (myFld1 IN (%CatPrompt(Prompt_MultUsrSlct)))
;
QUIT;

[Kurt_Bremser]

Your macro does not create any code, so your condition ends up as

myFld1 IN ()

[yabwon]

Hi,

1) you have a semicolon in the end `&emit;` make it . or

2) maybe this approach will help:

%macro CatPrompt (prmpt);
  %local i emit _dlm _count _prmpt;
  %let emit = "NOT-EXISTING-VALUE"; /* workaround for empty list */

  %if %bquote(&prmpt.) NE %then 
    %do;
      %LET emit = ;
      %LET _dlm = ;
      %LET _count = %QSYSFUNC(COUNTW(&prmpt.));
      %do i = 1 %to &_count;
        %LET _prmpt = %QSCAN(&prmpt., &i.);
        %let emit = &emit.&_dlm. %SYSFUNC(quote(&_prmpt.));
        %if &i. = 1 %then %LET _dlm =,;
      %end;
    %end;
&emit.
%mend CatPrompt;


%put *%CatPrompt(prmpt)*; /*single element list */

%put *%CatPrompt(A'A B'B C"C D"D E)*; /* multiple elements list with quotes in it */

%put *%CatPrompt()*; /* empty list */

%let list = a b c d;
%put *%CatPrompt(&list.)*; /* list in a macrovariable */

All the best

Bart

[Lior2020]

Using the macro I posted:
%put %CatPrompt(Prompt_MultUsrSlct);
The results in the log are:
'a','b','c'
so it looks like the macro is ok. am i right?

but the proc sql makes an error. here is the log (i simplified the where clause):

36 PROC SQL;
37 CREATE TABLE WORK.Tbl2 AS
38 SELECT *
39 FROM WORK.Tbl1
40 WHERE myFld1 IN (%CatPrompt(Prompt_MultUsrSlct))
NOTE: Line generated by the macro variable "EMIT".
40 'a','b','c'
_
22

_

76

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, (, -, SELECT.

ERROR 76-322: Syntax error, statement will be ignored.
41 ;

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
42 QUIT;

[yabwon · Accepted solution]

Hi,

try to make it:

%UNQUOTE(&emit.)

at the end of the macro

Bart

[s_lassen]

I assume you have macro variables like this:

%let a1=James;
%let a2=Anna;
%let a3=Culbert;
%let a_count=3;

And you want to do something like this:

proc sql;
  select name from sashelp.class where name in(%catprompt(a));
quit;

There seems to be some sort of macro quoting problem, meaning that the stuff in the IN clause does not resolve correctly. I was too lazy to find out what it was, so I just wrote a new version of the macro without quoting (the %STR gets unquoted by the %SYSFUNC):

%macro CatPrompt(prmpt);
  %local i delim;
  %do i=1 %to &&&prmpt._count;
     %do;&delim.%sysfunc(quote(&&&prmpt&i,%str(%')))%end;
     %let delim=,;
     %end;
%mend;

and that seems to work as intended.

The inner %DO...%END is just to avoid spurious whitespace in the output.

Edit added:

When you have input from users (they are unpredictable!) you may want to make sure that they do not break your precious macro by typing strange characters in the wrong places. So I did another version of the macro, getting the variable values using %SUPERQ (the macro quoting by %SUPERQ also gets neutralized by %SYSFUNC, and the QUOTE function makes sure that the quotes are balanced):

%macro CatPrompt(prmpt);
  %local i delim;
  %do i=1 %to &&&prmpt._count;
     %do;&delim.%sysfunc(quote(%superq(&prmpt.&i),%str(%')))%end;
     %let delim=,;
     %end;
%mend;