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'?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: