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.
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.
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.
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.
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 */.
I think that
end CUST_TYPE_CD,
(having a label after the end statement) isn't valid in SAS (untested).
Tom
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.