Convert SQL Code to SAS, specifically not like '8%'

[hoofbeats576]

I am using Enterprise Grid 6.1 and I need help converting the following SQL code to SAS, in particular the '8%' is giving me trouble:

select cc.RPQ_CLASS_KEY, cl.RPQ_CLASS_CD, substr(cl.RPQ_CLASS_DESC,1,50) as RPQ_CLASS_DESC, sic.SIC_CD,

                cc.ST_CD as CLASS_STATE, d.ST_CD as PROPER_STATE,

                from REF_DATA.RPQ_CLASS_CNTL cc

join REF_DATA.RPQ_PROPER_XREF d on cc.RPQ_CLASS_KEY = d.RPQ_CLASS_KEY and (cc.ST_CD = d.ST_CD or cc.ST_CD = 'CW' or d.ST_CD = 'CW') and d.SYS_ACTIVE = 'Y' and d.EXP_DT >= current date

join REF_DATA.RPQ_CLASS_LKUP cl on d.RPQ_CLASS_KEY = cl.RPQ_CLASS_KEY and cl.SYS_ACTIVE = 'Y'

left outer join (

                select GL_CLS_CODE as CLASS_CD, min(SIC_CD) as SIC_CD from REF_DATA.E_GL_SIC_ASSIGN where GL_CLS_CODE not like '8%' and SYS_ACTIVE = 'Y' group by GL_CLS_CODE

                union all

                select CLASS_CD, min(SIC_CD) as SIC_CD from REF_DATA.E_PROPER_RECD where CLASS_CD like '8%' and SYS_ACTIVE = 'Y' group by CLASS_CD

) sic on cl.RPQ_CLASS_CD = sic.CLASS_CD

where cc.SYS_ACTIVE = 'Y' and cc.EXP_DT >= current date

order by RPQ_CLASS_DESC, CLASS_STATE, PROPER_STATE

[Reeza]

NOT LIKE '8%' is valid SAS code. Your error is somewhere else. Can you please post the log so we can see the actual error? Once you've seen 1000 of them you learn what to look for 🙂

---

@hoofbeats576 wrote:

I am using Enterprise Grid 6.1 and I need help converting the following SQL code to SAS, in particular the '8%' is giving me trouble:

select cc.RPQ_CLASS_KEY, cl.RPQ_CLASS_CD, substr(cl.RPQ_CLASS_DESC,1,50) as RPQ_CLASS_DESC, sic.SIC_CD,

                cc.ST_CD as CLASS_STATE, d.ST_CD as PROPER_STATE,

                from REF_DATA.RPQ_CLASS_CNTL cc

join REF_DATA.RPQ_PROPER_XREF d on cc.RPQ_CLASS_KEY = d.RPQ_CLASS_KEY and (cc.ST_CD = d.ST_CD or cc.ST_CD = 'CW' or d.ST_CD = 'CW') and d.SYS_ACTIVE = 'Y' and d.EXP_DT >= current date

join REF_DATA.RPQ_CLASS_LKUP cl on d.RPQ_CLASS_KEY = cl.RPQ_CLASS_KEY and cl.SYS_ACTIVE = 'Y'

left outer join (

                select GL_CLS_CODE as CLASS_CD, min(SIC_CD) as SIC_CD from REF_DATA.E_GL_SIC_ASSIGN where GL_CLS_CODE not like '8%' and SYS_ACTIVE = 'Y' group by GL_CLS_CODE

                union all

                select CLASS_CD, min(SIC_CD) as SIC_CD from REF_DATA.E_PROPER_RECD where CLASS_CD like '8%' and SYS_ACTIVE = 'Y' group by CLASS_CD

) sic on cl.RPQ_CLASS_CD = sic.CLASS_CD

where cc.SYS_ACTIVE = 'Y' and cc.EXP_DT >= current date

order by RPQ_CLASS_DESC, CLASS_STATE, PROPER_STATE

---

[hoofbeats576]

This is the log summary:

PROC SQL;

24         select cc.RPQ_CLASS_KEY, cl.RPQ_CLASS_CD, substr(cl.RPQ_CLASS_DESC,1,50) as RPQ_CLASS_DESC, cl.IQ_PROPER_CD, sic.SIC_CD,

25                         cc.ST_CD as CLASS_STATE, d.ST_CD as PROPER_STATE,

26                         d.AB_PROPER_CD, d.AC_PROPER_CD, d.AP_PROPER_CD, d.AU_PROPER_CD, d.FR_PROPER_CD, d.AD_PROPER_CD,

26       ! d.AW_PROPER_CD

27         from REF_DATA.RPQ_CLASS_CNTL cc

28         join REF_DATA.RPQ_PROPER_XREF d on cc.RPQ_CLASS_KEY = d.RPQ_CLASS_KEY and (cc.ST_CD = d.ST_CD or cc.ST_CD = 'CW' or

28       ! d.ST_CD = 'CW') and d.SYS_ACTIVE = 'Y' and d.EXP_DT >= current date

28        join REF_DATA.RPQ_PROPER_XREF d on cc.RPQ_CLASS_KEY = d.RPQ_CLASS_KEY and (cc.ST_CD = d.ST_CD or cc.ST_CD = 'CW' or

28       ! d.ST_CD = 'CW') and d.SYS_ACTIVE = 'Y' and d.EXP_DT >= current date

                                                                          ____

                                                                         22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE,

             GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

28         join REF_DATA.RPQ_PROPER_XREF d on cc.RPQ_CLASS_KEY = d.RPQ_CLASS_KEY and (cc.ST_CD = d.ST_CD or cc.ST_CD = 'CW' or

28       ! d.ST_CD = 'CW') and d.SYS_ACTIVE = 'Y' and d.EXP_DT >= current date

                                                                         ____

                                                                         76

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

29         join REF_DATA.RPQ_CLASS_LKUP cl on d.RPQ_CLASS_KEY = cl.RPQ_CLASS_KEY and cl.SYS_ACTIVE = 'Y'

30         left outer join (

31                         select GL_CLS_CODE as CLASS_CD, min(SIC_CD) as SIC_CD from REF_DATA.E_GL_SIC_ASSIGN where GL_CLS_CODE not

31       ! like '8%' and SYS_ACTIVE = 'Y' group by GL_CLS_CODE

32                         union all

33                         select CLASS_CD, min(SIC_CD) as SIC_CD from REF_DATA.E_PROPER_RECD where CLASS_CD like '8%' and SYS_ACTIVE

33       ! = 'Y' group by CLASS_CD

34         ) sic on cl.RPQ_CLASS_CD = sic.CLASS_CD

35         where cc.SYS_ACTIVE = 'Y' and cc.EXP_DT >= current date

36         order by RPQ_CLASS_DESC, CLASS_STATE, PROPER_STATE

37         ;

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

38         QUIT;

NOTE: The SAS System stopped processing this step because of errors.

[Tom]

Use the INSERT CODE button in the forum editor to get a pop-up window where you and paste/edit the LOG.  Then the formatting is preserved. In particular what part of the code SAS has underlined.

Also formatting your original code can make it easier for you to spot structural errors.  For example you original posted code has an extra comma, although it look like perhaps that is not your actual error. 

A couple of places you have the random text CURRENT DATE in the middle of your SQL code. 

Did you mean perhaps to use DATE() or DATETIME() function call there, depending on whether the EXP_DT has date or datetime values?

select cc.RPQ_CLASS_KEY
     , cl.RPQ_CLASS_CD
     , substr(cl.RPQ_CLASS_DESC,1,50) as RPQ_CLASS_DESC
     , sic.SIC_CD
     , cc.ST_CD as CLASS_STATE
     , d.ST_CD as PROPER_STATE
     ,  /* <---- EXTRA COMMA HERE */

  from REF_DATA.RPQ_CLASS_CNTL cc
  join REF_DATA.RPQ_PROPER_XREF d
    on cc.RPQ_CLASS_KEY = d.RPQ_CLASS_KEY
   and (cc.ST_CD = d.ST_CD or cc.ST_CD = 'CW' or d.ST_CD = 'CW')
   and d.SYS_ACTIVE = 'Y'
   and d.EXP_DT >= current date  /* <-- INVALID SYNTAX */
  join REF_DATA.RPQ_CLASS_LKUP cl
    on d.RPQ_CLASS_KEY = cl.RPQ_CLASS_KEY
   and cl.SYS_ACTIVE = 'Y'
  left outer join 
   (select GL_CLS_CODE as CLASS_CD
         , min(SIC_CD) as SIC_CD
      from REF_DATA.E_GL_SIC_ASSIGN
      where GL_CLS_CODE not like '8%'
        and SYS_ACTIVE = 'Y'
      group by GL_CLS_CODE
    union all
    select CLASS_CD
         , min(SIC_CD) as SIC_CD
      from REF_DATA.E_PROPER_RECD
      where CLASS_CD like '8%'
        and SYS_ACTIVE = 'Y' 
      group by CLASS_CD
   ) sic 
    on cl.RPQ_CLASS_CD = sic.CLASS_CD
 where cc.SYS_ACTIVE = 'Y'
   and cc.EXP_DT >= current date  /* <-- INVALID SYNTAX */
 order by RPQ_CLASS_DESC, CLASS_STATE, PROPER_STATE
;

[hoofbeats576 · Accepted solution]

I found the issue, it was with the library prefix.  Thank you for the help and suggestion for formatting.