topic Re: Unable to pass an SQL query to Oracle property in SAS Procedures

Unable to pass an SQL query to Oracle property

asimraja — Fri, 10 Jul 2015 17:55:02 GMT

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.

Re: Unable to pass an SQL query to Oracle property

billfish — Fri, 10 Jul 2015 18:04:57 GMT

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.

Re: Unable to pass an SQL query to Oracle property

asimraja — Fri, 10 Jul 2015 19:38:49 GMT

Thank you, Billfish!