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
