Help using Base SAS procedures

Unable to pass an SQL query to Oracle property

Accepted Solution Solved
Reply
Contributor
Posts: 30
Accepted Solution

Unable to pass an SQL query to Oracle property

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.


Accepted Solutions
Solution
‎07-10-2015 02:04 PM
Contributor
Posts: 52

Re: Unable to pass an SQL query to Oracle property


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.

View solution in original post


All Replies
Solution
‎07-10-2015 02:04 PM
Contributor
Posts: 52

Re: Unable to pass an SQL query to Oracle property


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.

Contributor
Posts: 30

Re: Unable to pass an SQL query to Oracle property

Thank you, Billfish!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 415 views
  • 0 likes
  • 2 in conversation