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

I wanted to iteratively pass list of variables in quotes separated by commas to a SQL WHERE “IN”  clause

WHERE IDS in ('a111','a222','a333');

 

Please check below working piece of SAS macro code, first approach invokes macro directly using %, it is working as expected.

 

In the second approach; macro invocation using CALL EXECUTE, I am not able to pass multiple parameters without wrapping up in quotes

Else it will throw ERROR: More positional parameters found than defined

But as workaround I am able to remove the additional quote using a dequote inside the macro.

 

Can you suggest modification to CALL EXECUTE statement so that macro variable mbrlist receive values as 'a111','a222','a333' and not “'a111','a222','a333'”,

so that dequote statement is not needed, ie wrapping CALL executive parms or just macro parm with %NRSTR, %NRBQUOTE etc functions?

 


%macro get_dbdata(mbrlist)  ;
    %put &=mbrlist;
    %let mbrlist_DQ=%sysfunc(dequote(&mbrlist.));
    %put &=mbrlist_DQ;
%MEND;



/* 1. Call macro direct */

%let mbrlst='a111','a222','a333';
%put &=mbrlst;
%get_dbdata(%nrstr(&mbrlst));


/* 2. Call macro using Call execute */

data qq;
    /*   set indata; */


    mbr_list="'b111','b222','b333'";
    put mbr_list=;
    call execute('%get_dbdata("' || strip(mbr_list) || '")'  );;

run;
1 ACCEPTED SOLUTION

Accepted Solutions
Ninan
Obsidian | Level 7
Responses below from Astounding and Kurt are solutions
Response from Tom about using nested functions be considered as extension of what we have discussed here,

now this thread has many use-cases to explore for SAS MACRO learner!!
Thanks everyone for all your replies.

View solution in original post

7 REPLIES 7
Ninan
Obsidian | Level 7

I have tried %NRSTR in multiple combinations before but it was throwing different errors, may be because of unbalanced quotes, commas, and brackets

But rethinking with fresh mind it is working now -😊

 

call execute('%get_dbdata(%NRSTR(' || strip(mbr_list) || '))'  );;

 

Thank you Kurt

Astounding
PROC Star

It looks like you intend to use the list in your macro, something like:

 

where ids in (&mbr_list)

As long as you don't add %NRSTR in the wrong place, you don't need to pass the list of values as a macro parameter.  Instead, bypass all parameters:

ata qq;
    /*   set indata; */
    mbr_list="'b111','b222','b333'";
    put mbr_list=;
    call symputx('mbr_list', mbr_list);
    call execute('%get_dbdata');
run;

Within the definition of %get_dbdata, you can still use the WHERE clause that refers to &mbr_list.  It's just not a macro parameter anymore.

 

Note that there is a tricky timing issue here, that works for this problem.  On each observation:

 

  1. First, CALL SYMPUTX replaces &mbr_list
  2. Then CALL EXECUTE replaces references to &mbr_list within %get_dbdata with the current value of &mbr_list

 

 

Ninan
Obsidian | Level 7
Thank you for your response, this is an alternate solution to avoid the complexities of concatenating different parameter while calling the macro.
Tom
Super User Tom
Super User

To add the %NRSTR in the CALL EXECUTE wrap it in %NRSTR().

3110  data qq;
3111    mbr_list="'b111','b222','b333'";
3112    put mbr_list=;
3113    call execute(cats('%get_dbdata(%nrstr(%nrstr)(',mbr_list,'))'));
3114  run;

mbr_list='b111','b222','b333'
MBRLIST=%nrstr('b111','b222','b333')
MBRLIST_DQ='b111','b222','b333'

 

Ninan
Obsidian | Level 7
Responses below from Astounding and Kurt are solutions
Response from Tom about using nested functions be considered as extension of what we have discussed here,

now this thread has many use-cases to explore for SAS MACRO learner!!
Thanks everyone for all your replies.
Tom
Super User Tom
Super User

Here are two easier things to try if you are just going to use the list of values with the IN operator.

 

First:  Get rid of the commas.  The IN operator in SAS is just as happy to use spaces instead.

WHERE IDS in ('a111' 'a222' 'a333')

Second:  Include the parentheses in the parameter value.  That will protect the commas from being seen as marking new arguments.  Either put them in the macro variable value, or in the macro call.

%macro get_dbdate(mbr_list);
... 
where ids in &mbr_list 
...
%mend;
...
%get_dbdata( ('a111','a222','a333') );
...
call execute(cats('%get_dbdata((',mbr_list,'))'));;

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!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 685 views
  • 3 likes
  • 4 in conversation