Issue with Proc SQL Pass Through Facility

[asimraja]

Hi,

I'm trying to run an Oracle SQL query, which I inherited from somone, in the Proc SQL as a pass-through query. However, while I'm able to run the query in a stand-alone, I'm unable to run it in Proc SQL. I suspect that it has something to do with implicit conversions that Oracle performs but Proc SQL doesn't.

I've provided the SAS Code and Log below. Thank you in advance for your help!

SAS Code:

proc sql noprint;
    CONNECT to oracle (user="XXXX" pass="yyyyy" path='@yyyyy');
        CREATE table ratings_raw as
                select cusip_id, src, val, rtg_dt,
                  DECODE (rtg_typ, 'LT', 1, 'ST', 2, 3) as rtg_typ,
                  min( DECODE (rtg_typ, 'LT', 1, 'ST', 2, 3) ) over (partition by cusip_id) min_rtg_typ
                from
                (
                    select distinct c.cusip_id, rs.src, r.val, r.rtg_dt, r.rtg_typ, r.upd_dt,
                    max(r.upd_dt) over (partition by c.cusip_id, r.rtg_typ) max_upd_dt,
                    max(r.rtg_dt) over (partition by c.cusip_id, r.rtg_typ) max_rtg_dt,
                    DECODE (r.rtg_typ, 'LT', 1, 'ST', 2, 3) rtgtyp,
                    min( DECODE (r.rtg_typ, 'LT', 1, 'ST', 2, 3) ) over (partition by c.cusip_id) min_rtgtyp
                     from cusip_rtg_src_mapg c, rtg_src rs, rtgs_mthy_spst r
                     where c.rtg_src_oid = rs.rtg_src_oid
                    and r.cusip_rtg_src_mapg_oid = c.cusip_rtg_src_mapg_oid
                    and src='MOODY'
                    and r.RTG_CATG = 'RATING'
                    and r.hist_dt = to_date('20160630', 'YYYYMMDD')
                    and rtg_dt <= sysdate
            )
            where rtgtyp = min_rtgtyp
              and upd_dt = max_upd_dt
    );     
    disconnect from oracle;
QUIT;

Log:

62         proc sql noprint;
63             CONNECT to oracle
63       ! (user="xxxx" pass="yyyy" path='@XXXX');
64                 CREATE table ratings_raw as
65                         select cusip_id, src, val, rtg_dt,
66                           DECODE (rtg_typ, 'LT', 1, 'ST', 2, 3) as rtg_typ,
67                           min( DECODE (rtg_typ, 'LT', 1, 'ST', 2, 3) ) over (partition by cusip_id) min_rtg_typ
                                                              ____
                                                              22
                                                              76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

68                         from
69                         (
70                             select distinct c.cusip_id, rs.src, r.val, r.rtg_dt, r.rtg_typ, r.upd_dt,
71                             max(r.upd_dt) over (partition by c.cusip_id, r.rtg_typ) max_upd_dt,
72                             max(r.rtg_dt) over (partition by c.cusip_id, r.rtg_typ) max_rtg_dt,
73                             DECODE (r.rtg_typ, 'LT', 1, 'ST', 2, 3) rtgtyp,
74                             min( DECODE (r.rtg_typ, 'LT', 1, 'ST', 2, 3) ) over (partition by c.cusip_id) min_rtgtyp
75                              from cusip_rtg_src_mapg c, rtg_src rs, rtgs_mthy_spst r
76                              where c.rtg_src_oid = rs.rtg_src_oid
77                             and r.cusip_rtg_src_mapg_oid = c.cusip_rtg_src_mapg_oid
78                             and src='MOODY'
79                             and r.RTG_CATG = 'RATING'
80                             and r.hist_dt = to_date('20160630', 'YYYYMMDD')
81                             and rtg_dt <= sysdate
82                     )
83                     where rtgtyp = min_rtgtyp
84                       and upd_dt = max_upd_dt
85             );
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
86             disconnect from oracle;
NOTE: Statement not executed due to NOEXEC option.
87         QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.02 seconds

[Reeza · Accepted solution]

I think your pass thru query isn't set up quite correctly.

Its usually of the form:

Proc sql;

connection string....;

create table want as

select * from connection to Oracle as (

sql pass through query);

disconnect ...;

quit;

In your current query I'm not sure if your trying to create table on server or in SAS work. 

[asimraja]

Thank you, Reeza! That worked.