topic Re: Macro variables in SAS in SAS Procedures

Macro variables in SAS

archana — Tue, 02 Sep 2014 19:51:36 GMT

I am trying the below and it is not working. I need to pass this variable yrqtr to oracle query.

data _null_;
%let yy = %sysfunc(intnx(month,%sysfunc(today()),-12),yymmdd2.);

qtr=qtr(today()-30);

yrqtr=compress('Q'||qtr||yy||'Partner details');

call symput('yrqtr',yrqtr);
run;

I am trying to make Q32013 Partner Details and pass it to Oracle query.

Re: Macro variables in SAS

Reeza — Tue, 02 Sep 2014 19:54:06 GMT

Why do you have a %let for the first line?

And then in yrqtr you don't refer to it as a macro variable.

I'd suggest converting the first part to data step logic rather than macro logic and go from there.

Re: Macro variables in SAS

archana — Tue, 02 Sep 2014 20:04:47 GMT

even by removing %let it is throwing an error

Re: Macro variables in SAS

stat_sas — Tue, 02 Sep 2014 20:43:09 GMT

data _null_;

%let yy = %sysfunc(intnx(month,%sysfunc(today()),-12),year.);

qtr=qtr(today()-30);

yrqtr=compress('Q'||qtr||&yy)||' Partner details';

call symput('yrqtr',yrqtr);

run;

%put &yy &yrqtr;

Re: Macro variables in SAS

Tom — Tue, 02 Sep 2014 21:39:22 GMT

You have mixed macro code with data step code. Note there is no reason to put a %LET inside of DATA or PROC step. SAS will evaluate the %LET before it compiles the actual SAS code.

I think this is what you want although I am not sure what the -30 used on finding the quarter means. Is it possible that could mess up the result if you run this in January? Also I have removed the extra space between "Partner" and "details".

Here is one way to do it with macro logic.

%let yy = %eval(%sysfunc(today(),year4) - 1);

%let qtr = %sysfunc(putn(%sysfunc(today())-30,qtr));

%let yrqtr = Q&qtr.&yy Partner details;

%put yy=&yy qtr=&qtr yrqtr="&yrqtr" ;

Here is the same using a data step.

data _null_;

yy = year(today())-1 ;

qtr=qtr(today()-30);

yrqtr=catx(' ',cats('Q',qtr,yy),'Partner details');

put yy= qtr= yrqtr= :$quote.;

call symputx('yrqtr',yrqtr);

run;

Re: Macro variables in SAS

archana — Tue, 02 Sep 2014 23:48:07 GMT

Thank you Tom. Another question. I am trying to pass the above value - yrqtr to an oracle query but not working.

proc sql exec;

CREATE TABLE test AS select * from connection to oracle

(

select id,id1 from table where x = '&yrqtr.');

quit;

Re: Macro variables in SAS

Cynthia_sas — Wed, 03 Sep 2014 01:50:47 GMT

Hi:

Macro variable references will not resolve in single quotes.

cynthia

Re: Macro variables in SAS

Jagadishkatam — Wed, 03 Sep 2014 01:59:27 GMT

i agree with Cynthia.

The reason for this is that for the macro variable to resolve, the macro trigger has to recognize macro variable and then has to send the macro variable to macro processor and search the value of macro variable in the global symbol table. however the macro trigger will not recognize the macro variable if it is in single quotes. macro trigger recognizes macro variables in double quotes.

Thanks,

Jag