Passing a query as a string to a macro

[SyidaRox]

Hello - I am trying to pass a query (as a string) to a macro.  When I PUT the query variable this is what it looks like in the log.

QUERY_TXT=select a.cust_id, a.order_dt, prod_type, prod_subtype
from  VPS_PRM.order_data a
where a.order_dt = '2020-05-09'

This is the code that I am using...

string = cats('%nrstr(%RunReport(ISD=',strip(Start_Dt),',IED=',strip(End_Dt),',Days=',strip(Lookback_Days),',
    AT=',strip(Attachment_Type),',
    Query=',%quote(Query_Txt),'));');

This is the error that I am getting...

ERROR: All positional parameters must precede keyword parameters.

Subsequent errors point to the query_txt variable

ERROR 180-322: Statement is not valid or it is used out of proper order.

Any help to resolve my issue is much appreciated.

Thank you.

CALL EXECUTE(string);

[SyidaRox]

Just realized that all of that code that I intended to display did not make it to the code section.  Here it is again...

string = cats('%nrstr(%RunReport(ISD=',strip(Start_Dt),',IED=',strip(End_Dt),',Days=',strip(Lookback_Days),', AT=',strip(Attachment_Type),', Query=',%quote(Query_Txt),'));'); 

CALL EXECUTE(string); 

[Tom · Accepted solution]

Don't wrap the %NRSTR() around the whole macro call, just the %MACRONAME part.  Don't macro quote the NAME of the varaible with the SQL code, but around the value being passed to the macro call.  If you are using CATS() you don't need the STRIP() function calls.  Format your code so you have some hope of being able to understand it when you look at it again in two weeks.

Try this:

string = 
cats('%nrstr(%RunReport)(ISD=',Start_Dt
    ,',IED=',End_Dt
    ,',Days=',Lookback_Days
    ,',AT=',Attachment_Type
    ,',Query=%quote(',Query_Txt,')'
    ,');'
    )
; 

CALL EXECUTE(string); 

 But you might need to add %NRSTR() around the %QUOTE function call.

Or modify your macro to accept actual quotes around the value of the QUERY parameter.

%macro runreport(....,query=,...);
...%sysfunc(dequote(&query))...
%mend runreport;

 Then pass a quoted value to call execute:

   ,',Query=',quote(trim(Query_Txt))

[s_lassen]

There is obviously a comma or such in your Query_Txt variable. You put the %quote the wrong place, methinks. Try something like

string = cats('%RunReport(ISD=',strip(Start_Dt),',IED=',strip(End_Dt),',Days=',strip(Lookback_Days),',
    AT=',strip(Attachment_Type),',
    Query=%quote(',Query_Txt,'));');

I also dropped the %NRSTR around the whole expression, as the only way to execute that string is to %UNQUOTE it, and that will remove the %QUOTE around the Query_Txt.

[ballardw]

As a minimum you should show the actual code of the macro.

The message you show is typical when a value is  used as a parameter that includes commas. The macro parser uses the comma to delimit the macro parameters. You need to mask or quote the commas in some manner,

%quote(Query_Txt)

Is using

%quote(select a.cust_id, a.order_dt, prod_type, prod_subtype
from  VPS_PRM.order_data a
where a.order_dt = '2020-05-09')

as a call but the %quote function only allows a single parameter.

I strongly suspect that you do not want the %quote function but the data step Quote function.

[Kurt_Bremser]

 Why do you use the macro function %QUOTE? This will be resolved while the data step is compiled, not when the string is populated during data step execution. Use the data step function QUOTE instead:

%macro print(q=);
%put &q;
%mend;

data _null_;
string = cats('%nrstr(%print(q=',quote('a,b'),'))');
call execute(string);
run;

[AhmedAl_Attar]

@SyidaRox 

Your query_txt needs to be enclosed in a %str() to escape the embedded  ',' between your columns

Your RunReport macro, needs to use %unquote(&query) to cater for the character values

/* mock macro for illustration only */
%macro RunReport(isd=, ied=, days=, at=, query=);
	%put _user_;
	proc sql;
		%unquote(&query);
	quit;
%mend;

DATA _null_;
/* Sample data */
Start_Dt='06-01-2020';
End_Dt='06-02-2020';
Lookback_Days='1';
Attachment_Type='pdf';

string = cats('%RunReport(ISD='
,STRIP(Start_Dt)
,',IED='
,STRIP(End_Dt)
,',Days='
,STRIP(Lookback_Days)
,', AT='
,STRIP(Attachment_Type)
,', Query=%str('
,'select a.cust_id, a.order_dt, prod_type, prod_subtype from  VPS_PRM.order_data a where a.order_dt = %"2020-05-09%")'
,');'); 

CALL EXECUTE(string);
STOP; 
RUN;

Note: I split the CATS () function call across multiple lines for better readability

Hope this helps,

Ahmed    

[SyidaRox]

Thank you everyone for the assistance. I learned some new things today and I appreciate you all!