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
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.
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.
Thank you, Reeza! That worked.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.