BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
asimraja
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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. 

 

View solution in original post

2 REPLIES 2
Reeza
Super User

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
Fluorite | Level 6

Thank you, Reeza! That worked.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 2538 views
  • 2 likes
  • 2 in conversation