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?
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
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
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
)
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.