topic Re: Proc SQL within Macro error in SAS Programming

Proc SQL within Macro error

cstanyer — Mon, 14 Nov 2016 12:14:39 GMT

I'm trying to run some proc sql within a macro and SAS won't recognise my macro variables. What am I doing wrong...

%macro sixmonthroll(startdate,longstart,longend);

rsubmit;

proc sql;

create table &startdate. as

etc.

quit;

endrsubmit;

%sixmonthroll (jan16,jan_2016,jul_2016);

When I run the code above I get the following error:

224 %sixmonthroll (jan16,jan_2016,jul_2016);

NOTE: Remote submit to HOST commencing.

491 proc sql;

492 create table &startdate. as select

200

WARNING: Apparent symbolic reference STARTDATE not resolved.

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string.

Re: Proc SQL within Macro error

Kurt_Bremser — Mon, 14 Nov 2016 12:31:48 GMT

You will need to use the %syslput macro statement to transfer the values to the remote session.

Re: Proc SQL within Macro error

cstanyer — Mon, 14 Nov 2016 12:45:22 GMT

I wondered if it was something to do with that. I've not used %syslput before- I'll look into it, thank you.

Re: Proc SQL within Macro error

Kurt_Bremser — Mon, 14 Nov 2016 12:52:43 GMT

While macro statements like %let or %put or %if...%then...%else are executed locally, macro variable references are transferred to the remote session "as is" and resolved there, so one needs to create the macro variables in the remote session before using them.

Re: Proc SQL within Macro error

cstanyer — Mon, 14 Nov 2016 12:59:23 GMT

Could I move the rsubmits from within the macro code and rsubmit the %sixmonthroll instead?

Re: Proc SQL within Macro error

Kurt_Bremser — Mon, 14 Nov 2016 13:35:46 GMT

@cstanyer wrote:
Could I move the rsubmits from within the macro code and rsubmit the %sixmonthroll instead?

That would only work if you also defined the macro remotely:

rsubmit;
%macro testmac(parameters);
/* statements */
%mend;
%testmac(params);
endrsubmit;

Re: Proc SQL within Macro error

cstanyer — Mon, 14 Nov 2016 15:54:36 GMT

That's what I did. Thanks for the help!