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
)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.