09-02-2014 03:51 PM
I am trying the below and it is not working. I need to pass this variable yrqtr to oracle query.
%let yy = %sysfunc(intnx(month,%sysfunc(today()),-12),yymmdd2.);
I am trying to make Q32013 Partner Details and pass it to Oracle query.
09-02-2014 03:54 PM
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.
09-02-2014 04:43 PM
%let yy = %sysfunc(intnx(month,%sysfunc(today()),-12),year.);
yrqtr=compress('Q'||qtr||&yy)||' Partner details';
%put &yy &yrqtr;
09-02-2014 05:39 PM
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.
yy = year(today())-1 ;
yrqtr=catx(' ',cats('Q',qtr,yy),'Partner details');
put yy= qtr= yrqtr= :$quote.;
09-02-2014 07:48 PM
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.');
09-02-2014 09:59 PM
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.