Hi,
I'm trying to execute the following query on Oracle as the results are too big to pass as a SAS query. Can you please tell me what I'm doing wrong? When I run this query in a SQL query tool (Toad), it runs fine there but it gives me an error when I call within SAS. Code and SAS log error message are below. Thank you very much for your help!
Asim
Code:
PROC SQL;
/* Oracle direct connection -- Regular SQL overloads the SAS server's memory */
connect to oracle (user=&user password=&password path=POR_D601) ;
create table AMTM_Prices as
select * from connection to Oracle
(
select fa_mtm_eff_dt,secu_cusip_id, fa_mtm_prc_pct
from fa_mark f
where fa_mtm_eff_dt>= '01-JAN-2013'd and trd_stat_cd = 5 and secu_cusip_id is not null
order by f.secu_cusip_id
);
disconnect from oracle;
QUIT;
Log Error Message:
Error: Oracle prepare error: ORA-00933: SQL command not properly ended.
I beleive the issue is that one should not be using SAS-specific formats or functions within Oracle space.
From:
http://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql
one can get some hints such as:
select employee_id
from employee
where employee_date_hired > to_date('31-12-95','DD-MM-YY')
Note: '2Jan2015'd is SAS-specific.
I beleive the issue is that one should not be using SAS-specific formats or functions within Oracle space.
From:
http://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql
one can get some hints such as:
select employee_id
from employee
where employee_date_hired > to_date('31-12-95','DD-MM-YY')
Note: '2Jan2015'd is SAS-specific.
Thank you, Billfish!
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.