Help using Base SAS procedures

Macro variables in SAS

Reply
Contributor
Posts: 31

Macro variables in SAS

Hi

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.

Super User
Posts: 19,772

Re: Macro variables in SAS

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.

Contributor
Posts: 31

Re: Macro variables in SAS

even by removing %let it is throwing an error

Trusted Advisor
Posts: 1,228

Re: Macro variables in SAS

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;

Super User
Super User
Posts: 7,039

Re: Macro variables in SAS

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;

Contributor
Posts: 31

Re: Macro variables in SAS

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;

SAS Super FREQ
Posts: 8,864

Re: Macro variables in SAS


Hi:

  Macro variable references will not resolve in single quotes.

cynthia

Trusted Advisor
Posts: 1,137

Re: Macro variables in SAS

Posted in reply to Cynthia_sas

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

Thanks,
Jag
Ask a Question
Discussion stats
  • 7 replies
  • 287 views
  • 2 likes
  • 6 in conversation