Help using Base SAS procedures

proc sql with connection to oracle: to_date with macro variable

Reply
New Contributor
Posts: 4

proc sql with connection to oracle: to_date with macro variable

within PROC SQL WITH CONNECTION TO ORACLE:
I am trying to replace typing in a date with a macro variable.

Instead of:
WHERE ( DATE_ADDED_TRANSACTION BETWEEN TO_DATE('20110101000000','YYYYMMDDHH24MISS')
AND TO_DATE('20110131000000','YYYYMMDDHH24MISS') )

I want:
WHERE( DATE_ADDED_TRANSACTION BETWEEN TO_DATE("&STRT_DT.000000",'YYYYMMDDHH24MISS')
AND TO_DATE("&END_DT.000000",'YYYYMMDDHH24MISS') )

But when I switch to double-quotes I get an error:
ORACLE prepare error: ORA-00904: "20110101000000": invalid identifier.

So the macro variable appears to be resolving correctly, but it doesn't like the double quotes?
Frequent Contributor
Posts: 139

Re: proc sql with connection to oracle: to_date with macro variable

Oracle does not accept double quotes. All strings and dates need to be wrapped in single quotes.

You'll have to use something like this below


%let ora_today=%str(%')%sysfunc(putn(%sysfunc(today()),yymmddn8.))000000%str(%');
%put ora_today=&ora_today;


Also I don't think you need the to_char Oracle is smart enough to distinguish between dates and datetime.

Try this


%let ora_today2=%str(%')%sysfunc(putn(%sysfunc(today()),date11.))%str(%');
%put ora_today2=&ora_today2;


Regards
Darryl
Frequent Contributor
Frequent Contributor
Posts: 94

Re: proc sql with connection to oracle: to_date with macro variable

I've used something similar to Darryl's approach, using %nbrquote rather than %str (I'm not sure what difference this makes). This gets around the issue that Oracle can't use double quotes, but that SAS won't decode a macro value inside single quotes.

where
History.Date between TO_DATE(%nrbquote(')&StartDateTime.%nrbquote('), 'DD:MM:YYYY:HH24:MISmiley FrustratedS')
and TO_DATE(%nrbquote(')&FinalDateTime.%nrbquote('), 'DD:MM:YYYY:HH24:MISmiley FrustratedS')


My StartDateTime and FinalDateTime macros are formatted as datetime20., but you could change this to your own format quite easily.

For example I might use:

data _null_;
call symput('StartDateTime',put(intnx('dtday',datetime(),-1),datetime20.));
call symput('FinalDateTime',put(intnx('dtday',datetime(),-1,'end'),datetime20.));
run;

%put &StartDateTime. &FinalDateTime.;
New Contributor
Posts: 4

Re: proc sql with connection to oracle: to_date with macro variable

DF and Darryl. Thanks so much. Worked perfectly. I was ready to give up and type in my date range for each run.

Yeah for efficiency!
Super Contributor
Posts: 578

Re: proc sql with connection to oracle: to_date with macro variable

I think its easier to create the macro variable such that it includes the TO_DATE() as well. Then you can use use the variable plainly as

data work.dbdate;
format
dbvalue1 date9.
dbvalue2 date9.;
input dbvalue1:date9. dbvalue2:date9.;
cards;
30nov2010 31dec2010
run;


data _null_;
set work.dbdate;
call symput('DAY1',"TO_DATE('"||PUT(DBVALUE1,YYMMDDN8.)||"','YYYYMMDD')");
call symput('DAY2',"TO_DATE('"||PUT(DBVALUE2,YYMMDDN8.)||"','YYYYMMDD')");
RUN;

proc sql;
connect to oracle as db(xxxxxxxxxxxxx);

create table tst as select * from connection to db (
select * from mdm.dbdates
where date_value between &day1 and &day2
);

disconnect from db;
quit;


The oracle prepared statement is:
select * from mdm.dbdates where date_value between TO_DATE('20101130','YYYYMMDD') and TO_DATE('20101231','YYYYMMDD')
Ask a Question
Discussion stats
  • 4 replies
  • 2519 views
  • 0 likes
  • 4 in conversation