Unable to pass an SQL query to Oracle property

[asimraja]

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.

[billfish · Accepted solution]

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.

[asimraja]

Thank you, Billfish!