ERROR 22-322: Syntax error with case statement

[kjfswat]

Hello. I'm trying to figure out why I'm getting this error, which seems to be connected to the case/when statement but it runs fine in Toad. 

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

select i.CUSTOMER_ACCOUNT_KEY,i.SITE_ID, i.SITE_DESC, i.CUSTOMER_KEY, i.ACCOUNT_NUMBER, i.CUSTOMER_NAME, i.HOME_AREA_CODE, i.HOME_PHONE_NUMBER,i.CUSTOMER_STATUS_CODE,
case
when a.CUSTOMER_CATEGORY_CODE = 'T'
then 'Test'
else 'Customer'
end CUST_CAT_CODE,

case CUSTOMER_TYPE_CODE
when 'V'
then 'Employee'
when '4'
then 'Memo'
when '5'
then 'Memo'
when 'I'
then 'Internal'
else 'Default'
end CUST_TYPE_CD,

e.EQUIPMENT_STATUS_DESC, e.ITEM_NBR, e.ITEM_DESC, e.ICOMS_SERIAL_NBR, e.START_DT_KEY, e.END_DT_KEY,
case END_DT_KEY
when 99991231
then 'N'
else 'Y'
end SWAP_STATUS
from CM.ALL_INDIVIDUALS i
join CM.ALL_ACCOUNTS a on i.CUSTOMER_ACCOUNT_KEY = a.CUSTOMER_ACCOUNT_KEY
join CM.ALL_CUST_EQUIPMENT e on i.CUSTOMER_ACCOUNT_KEY = e.CUSTOMER_ACCOUNT_KEY
where ITEM_NBR in ('MPX032','CPX032') AND
e.END_DT_KEY > 20180401 AND
--CUSTOMER_TYPE_CODE = '%Default%' and
--i.SITE_ID in (1, 334, 541, 333, 334 ) AND
--e.EQUIPMENT_STATUS_DESC = 'INSTALLED AT CUSTOMER'
i.CUSTOMER_ACCOUNT_KEY IN('1051947001',
'1008410505')

order by end_dt_key DESC
--and a.CUSTOMER_CATEGORY_CODE = 'Customer'
--AND e.END_DT_KEY = 99991231;

---------------------------------------------------------------

Error:

26 case

27 when a.CUSTOMER_CATEGORY_CODE = 'T'

28 then 'Test'

29 else 'Customer'

30 end CUST_CAT_CODE,

_____________

22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS,

CONTAINS, EQ, EQT, FROM, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

31

32 case CUSTOMER_TYPE_CODE

33 when 'V'

____

22

ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT,

JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

34 then 'Employee'

35 when '4'

33 when 'V'

____

76

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

[Tom]

If you don't post the log messages using the {i} icon to open a pop-up window then the Forum editor mangles the formatting of the message and make it almost impossible to see what SAS is complaining about.

[ballardw]

Please post code and log entries into a code box opened using the forum's {i} icon. The code box will preserve formatting of text and in the case of error message the position of the error which is often indicated with underscore characters.

Your first error I think is that for CASE statements SAS really wants to see the AS at the end such as the "end as Region" below.

   select Name, case
                when Continent = 'North America' then 'Continental U.S.'
                when Continent = 'Oceania' then 'Pacific Islands'
                else 'None'
                end as Region
      from states;

Since the first CASE statement had an error then the following error messages could be confusion due to the nature of the first error.

If your code actually contains -- characters in this code:

e.END_DT_KEY > 20180401 AND
--CUSTOMER_TYPE_CODE = '%Default%' and
--i.SITE_ID in (1, 334, 541, 333, 334 ) AND
--e.EQUIPMENT_STATUS_DESC = 'INSTALLED AT CUSTOMER'
i.CUSTOMER_ACCOUNT_KEY IN('1051947001',
'1008410505')

remove them.

[Tom]

Why do you have double minus signs in your code?

If you negate a number twice it is the same as doing nothing.

order by end_dt_key DESC
--and a.CUSTOMER_CATEGORY_CODE = 'Customer'
--AND e.END_DT_KEY = 99991231;

If you want to list more variables for your ORDER BY clause you need have commas between them.

If you want to comment out code you need to use /* ... */ comments.

[SASKiwi]

I know you can use "--" two hyphens to comment a code line in Toad, but that is not valid in SAS. Change that to enclosing the code in /* code line */. 

[TomKari]

I think that

end CUST_TYPE_CD,

(having a label after the end statement) isn't valid in SAS (untested).

Tom