topic Re: Passing a query as a string to a macro in SAS Programming

Passing a query as a string to a macro

SyidaRox — Wed, 24 Jun 2020 15:25:02 GMT

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

Re: Passing a query as a string to a macro

SyidaRox — Wed, 24 Jun 2020 15:31:36 GMT

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

Re: Passing a query as a string to a macro

s_lassen — Wed, 24 Jun 2020 15:37:35 GMT

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.

Re: Passing a query as a string to a macro

ballardw — Wed, 24 Jun 2020 15:38:15 GMT

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.

Re: Passing a query as a string to a macro

Kurt_Bremser — Wed, 24 Jun 2020 15:45:11 GMT

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;

Re: Passing a query as a string to a macro

Tom — Wed, 24 Jun 2020 16:31:10 GMT

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))

Re: Passing a query as a string to a macro

AhmedAl_Attar — Wed, 24 Jun 2020 16:05:43 GMT

@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

Re: Passing a query as a string to a macro

SyidaRox — Wed, 24 Jun 2020 17:16:43 GMT

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