CODEWITHSUNDEEP
oracle-databasesas
user1286929
user1286929

Reputation: 21

Proper syntax for SAS macro date in Oracle query

I am trying to query the past week's additions to an Oracle database overnight and need to use macros to populate the dates. I am able to run the query below if I hard-code the actual dates. I've tried double and single quotes on the macro vars &sd and &ed. Please advise.

data _null_;
sd = dhms(today()-7,00,00,00);
ed = dhms(today()-1,23,59,59);
call symput("sd", put(sd, datetime20.));
call symput("ed", put(ed, datetime20.));
run;
%put &sd &ed;

proc sql;
connect to oracle (user=x password=x path=x);
create table weekly_test as
select * from connection to oracle
(select * from x.Estimates
where state_fips_code = '41' 
and altered_date between 
    to_date('&sd','DDMONYYYY:HH24:MI:SS')
    and to_date('&ed','DDMONYYYY:HH24:MI:SS'));
disconnect from oracle;
quit;

error

ORACLE execute error: ORA-01858: a non-numeric character was found where a numeric was expected.

and with double quotes

and altered_date between 
    to_date("&sd",'DDMONYYYY:HH24:MI:SS')
    and to_date("&ed",'DDMONYYYY:HH24:MI:SS'));

this error

ERROR: ORACLE prepare error: ORA-00904: "  21MAR2012:23:59:59": invalid identifier. SQL
statement: select * from X.Estimates where state_fips_code = '41' and altered_date
between to_date("  15MAR2012:00:00:00",'DDMONYYYY:HH24:MI:SS') and to_date("
21MAR2012:23:59:59",'DDMONYYYY:HH24:MI:SS').

Upvotes: 2

Views: 5949

Answers (3)

Chris J
Chris J

Reputation: 7769

This works....

%LET SD = %SYSFUNC(intnx(day,"&SYSDATE9"d,-7,b),date9.) ;
%LET ED = %SYSFUNC(intnx(day,"&SYSDATE9"d,-1,b),date9.) ;
%PUT &SD &ED ;

proc sql ;
  connect to oracle (user=x password=x path=x);
  create table weekly_test as
  select * from connection to oracle
  (select * from x.Estimates
   where state_fips_code = '41'
     and altered_date between %BQUOTE('&SD') and %BQUOTE('&ED')
  );
  disconnect from oracle ;
quit ;

Upvotes: 0

user1286929
user1286929

Reputation: 21

Many thanks Bob. I tried the code you posted and got ORA-01861: literal does not match format string. Anyway you got me thinking on the right path. I just added code to put single quotes around my dates in the data step and it worked. For anyone with similar problems code is below.

data _null_;
sd = dhms(today()-7,00,00,00);
ed = dhms(today()-1,23,59,59);
call symput('sd',"'"|| trim(left(put(sd, datetime20.)))||"'");
call symput('ed', "'"||trim(left(put(ed, datetime20.)))||"'");
run;
%put &sd &ed;

proc sql;
connect to oracle (user=x password=x path=x);
create table weekly_test as
select * from connection to oracle
(select * from x.Estimates
where state_fips_code = '41' 
and altered_date between 
    to_date(&sd,'DDMONYYYY:HH24:MI:SS')
    and to_date(&ed,'DDMONYYYY:HH24:MI:SS'));
disconnect from oracle;
quit;

Upvotes: 0

BellevueBob
BellevueBob

Reputation: 9618

The best bet is to define your macro variable with single quotes around the values. In fact, I don't think it's necessary to format it as a datetime literal; just construct a normal ANSI date string (YYYY-MM-DD) and you can also get rid of the TO_DATE function call.

For example, try these two statements:

%let SD=%str(%')%sysfunc( putn( %sysfunc(intnx(day,%sysfunc(today()) ,-7)),yymmdd10.))%str(%'); 
%let ED=%str(%')%sysfunc( putn( %sysfunc(intnx(day,%sysfunc(today()) ,-1)),yymmdd10.))%str(%');

Those define SD as today()-7 and ED as today()-1 (using pure macro code rather than a data step). Then, in your query, reference these macro variables unquoted:

proc sql;
connect to oracle (user=x password=x path=x);
create table weekly_test as
select * from connection to oracle
(select * from x.Estimates
where state_fips_code = '41' 
and altered_date between &sd and &ed
);
disconnect from oracle;
quit;

Upvotes: 2

Related Questions