macro CALL EXECUTE- ERROR: More positional parameters found than defined

[Ninan]

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;

[Kurt_Bremser]

You need to include the %NRSTR in the CALL EXECUTE.

[Ninan]

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]

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]

Thank you for your response, this is an alternate solution to avoid the complexities of concatenating different parameter while calling the macro.

[Tom]

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 · Accepted solution]

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]

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,'))'));;