topic Re: passing variable to oracle query in SAS Programming

passing variable to oracle query

jepafob — Fri, 19 Jun 2020 11:52:33 GMT

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.

Re: passing variable to oracle query

s_lassen — Fri, 19 Jun 2020 12:47:14 GMT

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'?

Re: passing variable to oracle query

smantha — Fri, 19 Jun 2020 12:51:15 GMT

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.