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.
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'?
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'?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.