BookmarkSubscribeRSS Feed
archana
Fluorite | Level 6

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.

7 REPLIES 7
Reeza
Super User

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.

archana
Fluorite | Level 6

even by removing %let it is throwing an error

stat_sas
Ammonite | Level 13

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;

Tom
Super User Tom
Super User

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;

archana
Fluorite | Level 6

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;

Cynthia_sas
SAS Super FREQ


Hi:

  Macro variable references will not resolve in single quotes.

cynthia

Jagadishkatam
Amethyst | Level 16

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 969 views
  • 2 likes
  • 6 in conversation