DATA Step, Macro, Functions and more

DateTime Parameter in Oracle PROC SQL

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

DateTime Parameter in Oracle PROC SQL

Hi!!

I have three tables:

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

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

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?


Accepted Solutions
Solution
‎03-19-2014 03:05 PM
Super User
Posts: 5,434

Re: DateTime Parameter in Oracle PROC SQL

Posted in reply to DavidCaliman

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

Data never sleeps

View solution in original post


All Replies
Solution
‎03-19-2014 03:05 PM
Super User
Posts: 5,434

Re: DateTime Parameter in Oracle PROC SQL

Posted in reply to DavidCaliman

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

Data never sleeps
Super User
Super User
Posts: 7,071

Re: DateTime Parameter in Oracle PROC SQL

Posted in reply to DavidCaliman

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

)

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 1488 views
  • 0 likes
  • 3 in conversation