DateTime Parameter in Oracle PROC SQL

[DavidCaliman]

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?

[LinusH · Accepted solution]

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

[Tom]

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

)