Issue with Proc SQL Pass Through Facility

asimraja — Wed, 10 Aug 2016 21:11:47 GMT

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

Re: Issue with Proc SQL Pass Through Facility

Reeza — Thu, 11 Aug 2016 00:53:56 GMT

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.

Re: Issue with Proc SQL Pass Through Facility

asimraja — Fri, 12 Aug 2016 15:20:23 GMT

Thank you, Reeza! That worked.

topic Issue with Proc SQL Pass Through Facility in SAS Procedures

Issue with Proc SQL Pass Through Facility

Re: Issue with Proc SQL Pass Through Facility

Re: Issue with Proc SQL Pass Through Facility