passing variable to oracle query

[jepafob]

Hi, 

I have this piece of code: 

proc sql;
&oraconnect;
execute (create table bc as
select      
a.key_num,
from warehouse_gbr.vtb_acct a

where a.dat_chrg_off >= to_date('01MAY2020','DDMMYYYY')
and a.dat_chrg_off < to_date('01JUN2020','DDMMYYYY')

order by a.key_num)
by ora ;
disconnect from ora;
quit;

and I am trying to pass those variables date1 instead of May date and date2 instead of June date (see below).

Data _NULL_;
 call symput('date1',put(intnx('month',today(),0),date9.));
 call symput('date2',put(intnx('month',today(),1),date9.));
Run;
%put &date1;
%put &date2;

Is there a way to do this?  I tried multiple things and they do not work.

[s_lassen · Accepted solution]

The Oracle SQL interpreter does not recognize stuff in double quotes as constants, but as identifiers. So you will have to get your macro values in single quotes, e.g.:

Data _NULL_;
 call symputx('date1',cats("'",put(intnx('month',today(),0),date9.),"'"));
 call symputx('date2',cats("'",put(intnx('month',today(),1),date9.),"'"));
Run;
%put &date1;
%put &date2;

You can the use that in the query:

proc sql;
&oraconnect;
execute (create table bc as
select      
a.key_num,
from warehouse_gbr.vtb_acct a

where a.dat_chrg_off >= to_date(&date1,'DDMMYYYY')
and a.dat_chrg_off < to_date(&date2,'DDMMYYYY')

order by a.key_num)
by ora ;
disconnect from ora;
quit;

But I am not quite sure about the second parameter to your TO_DATE function, should it not be 'DDMONYYYY'?

[smantha]

There is current_date and add months feature in oracle can’t you use them? Can’t you put double quotes around macro variables to_date(“&date1.”,'DDMMYYYY').
What did you try and did not work.