topic Re: proc sql with connection to oracle: to_date with macro variable in SAS Procedures

proc sql with connection to oracle: to_date with macro variable

ddeb — Mon, 07 Mar 2011 19:21:28 GMT

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?

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

darrylovia — Mon, 07 Mar 2011 20:22:00 GMT

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

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

DF — Tue, 08 Mar 2011 13:00:42 GMT

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:MI:SS')
and TO_DATE(%nrbquote(')&FinalDateTime.%nrbquote('), 'DD:MM:YYYY:HH24:MI:SS')

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.;

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

ddeb — Tue, 08 Mar 2011 14:23:15 GMT

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!

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

DBailey — Tue, 08 Mar 2011 14:31:13 GMT

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