BookmarkSubscribeRSS Feed
kjfswat
Calcite | Level 5

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.

5 REPLIES 5
Tom
Super User Tom
Super User

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
Super User

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
Super User Tom
Super User

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
PROC Star

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
Onyx | Level 15

I think that

end CUST_TYPE_CD,

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

 

Tom

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 4360 views
  • 0 likes
  • 5 in conversation