topic DateTime Parameter in Oracle PROC SQL in SAS Programming

DateTime Parameter in Oracle PROC SQL

DavidCaliman — Wed, 19 Mar 2014 18:54:42 GMT

Hi!!

I have three tables:

ODS.DATE (SAS TABLE) - Column DATE (numeric 😎 format DATETIME20.

ODS.CUSTOMER (SAS TABLE) - Column ID (numeric 😎

ORA.CUSTOMER (ORACLE TABLE) - Column ID (number), Column DAT (date)

I need to run this code:

PROC SQL NOPRINT;

select date into :LOAD_DATE from ODS.DATE

QUIT;

proc sql;

connect to oracle as conn (path='XXXX' user=XXXXX password="XXXXX");

insert into ODS.CUSTOMER

select ID from connection to conn (

select ID from ORA.CUSTOMER where dat >= "&LOAD_DATE"

);

disconnect from conn;

quit;

The following error is occurring:

- ERROR: ORACLE prepare error: ORA-00904: " 01JAN1900:00:00:00": invalid identifier. SQL statement: select * from ORA.CUSTOMER where dat >= " 01JAN1900:00:00:00".

Does anyone know how I Can pass the parameter correctly for Oracle?

Re: DateTime Parameter in Oracle PROC SQL

LinusH — Wed, 19 Mar 2014 19:05:40 GMT

Where clauses are usually pushed to Oracle using implicit pass-thru, the SAS/ACCESS engine will translate the date constant for you.

You need to add a format specification in the select into: clause (date9. - given this is a SAS date), and add a d when you refer to it: "&LOAD_DATE"d

Re: DateTime Parameter in Oracle PROC SQL

Tom — Wed, 19 Mar 2014 20:39:25 GMT

Here is a link to Oracle documentation on datetime literals. Literals Example: DATE '1998-12-25'

(Note that unlike SAS Oracle only has DATETIME and does not have separate DATE type).

select cats("DATE '",put(datepart(date),YYMMDD10.),"'") into :LOAD_DATE from ODS.DATE

...

select ID from connection to conn (

select ID from ORA.CUSTOMER where dat >= &LOAD_DATE

)