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

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

hoofbeats576 — Mon, 03 Jun 2019 16:05:53 GMT

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

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

Reeza — Mon, 03 Jun 2019 16:28:48 GMT

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

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

hoofbeats576 — Mon, 03 Jun 2019 16:42:51 GMT

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

____

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

____

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.

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

Tom — Mon, 03 Jun 2019 17:08:55 GMT

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
;

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

hoofbeats576 — Mon, 03 Jun 2019 17:29:12 GMT

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