topic Re: PROC SQL: CASE WHEN statement ERROR in SAS Procedures

PROC SQL: CASE WHEN statement ERROR

sufiya — Fri, 17 Jul 2020 00:03:31 GMT

Hello,

Hoping someone can help... trying to execute a Case When statement but I keep receiving this error message:

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

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

Below is the PROC SQL code and attached is the data - I have no idea what I am missing 😞

Thank you !!

PROC SQL;
CREATE TABLE BUCKETS AS 
 
SELECT DISTINCT 
a.MONTH, 
a.YEAR, 
a.ANIMAL, 
a.BAN, 
a.VOLUME, 
a.SIGHTINGS,
(case 
when a. SIGHTINGS >= 0  and a. SIGHTINGS <=  49.99 then '1.0-49'   
when >= 50  a.SIGHTINGS <= 99.99 then '2.50-99'
when >= 100 a. SIGHTINGS <= 249.99 then '3.100-249'
when >= 250 a.SIGHTINGS <= 499.99 then '4.250-499'
when >= 500 a. SIGHTINGS <=999.99 then '5.500-999'
when a. SIGHTINGS >= 1000 then '6.1000+'
else '' end) as Buckets

FROM WORK.MERGE a
     
GROUP BY 
a.Month, 
a.YEAR, 
a.ANIMAL, 
a.BAN, 
a.VOLUME, 
a.SIGHTINGS

;QUIT;

Re: PROC SQL: CASE WHEN statement ERROR

SASKiwi — Fri, 17 Jul 2020 00:29:20 GMT

Remove the spaces between "a." and "Sightings".

Re: PROC SQL: CASE WHEN statement ERROR

Kurt_Bremser — Fri, 17 Jul 2020 08:20:58 GMT

Aside from the spurious blanks, you should create a format with ranges; your CASE clause might miss values with imprecisions caused by binary calculation.

Re: PROC SQL: CASE WHEN statement ERROR

sufiya — Fri, 17 Jul 2020 12:17:22 GMT

Hello @SASKiwi , I did that and still getting the same error message.

Re: PROC SQL: CASE WHEN statement ERROR

PaigeMiller — Fri, 17 Jul 2020 12:26:20 GMT

@sufiya wrote:

Hello @SASKiwi , I did that and still getting the same error message.

Show us the log for this PROC SQL. Include the code as shown in the log, plus any NOTEs, ERRORs and WARNINGs. (For future reference, when code produces an error, you always need to show us the log as described, including the code)

Please click on the </> icon and paste this part of the log into the window that appears. This will allow the SAS Community software to format the log properly and make it more readable. Please do not skip this step.

Re: PROC SQL: CASE WHEN statement ERROR

sufiya — Fri, 17 Jul 2020 14:01:29 GMT

@PaigeMiller , please see below for the full error message. Thank you,

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

37         when a. SIGHTINGS >= 0  and a. SIGHTINGS <=  49.99 then '1.0-49'   
           ____
           22
           76
ERROR 22-322: Syntax error, expecting one of the following: a name, (, ',', '.', ANSIMISS, AS, CROSS, FULL, INNER, JOIN, LEFT, 
              NATURAL, NOMISS, RIGHT.  

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

38         when >= 50  a.SIGHTINGS <= 99.99 then '2.50-99'
39         when >= 100 a. SIGHTINGS <= 249.99 then '3.100-249'
40         when >= 250 a.SIGHTINGS <= 499.99 then '4.250-499'
41         when >= 500 a. SIGHTINGS <=999.99 then '5.500-999'
42         when a. SIGHTINGS >= 1000 then '6.1000+'
43         else '' end) as Buckets
44

Re: PROC SQL: CASE WHEN statement ERROR

ballardw — Fri, 17 Jul 2020 14:33:18 GMT

@sufiya wrote:

@PaigeMiller , please see below for the full error message. Thank you,

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

37         when a. SIGHTINGS >= 0  and a. SIGHTINGS <=  49.99 then '1.0-49'   
           ____
           22
           76
ERROR 22-322: Syntax error, expecting one of the following: a name, (, ',', '.', ANSIMISS, AS, CROSS, FULL, INNER, JOIN, LEFT, 
              NATURAL, NOMISS, RIGHT.  

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

38         when >= 50  a.SIGHTINGS <= 99.99 then '2.50-99'
39         when >= 100 a. SIGHTINGS <= 249.99 then '3.100-249'
40         when >= 250 a.SIGHTINGS <= 499.99 then '4.250-499'
41         when >= 500 a. SIGHTINGS <=999.99 then '5.500-999'
42         when a. SIGHTINGS >= 1000 then '6.1000+'
43         else '' end) as Buckets
44

That is NOT the complete Proc SQL code. Start with the Proc SQL statement and go to the Quit.

The most likely thing is that you do not have a COMMA appearing before the CASE and/or should not start with ( . Everything on the select clause needs to be separated with a comma.

That is why the diagnostic character _ appears under the ( character at the beginning of your case. That is the first position SAS found some problem. But it depends on what come before.

Re: PROC SQL: CASE WHEN statement ERROR

PaigeMiller — Fri, 17 Jul 2020 15:17:34 GMT

@sufiya wrote:

@PaigeMiller , please see below for the full error message. Thank you,

I want to see the COMPLETE LOG for PROC SQL, all of it, 100%, no exceptions, starting at the PROC SQL command, and then all the code, all the way down to the end of the PROC, and of course we need to see the ERROR, WARNING and NOTE messages, even if they are after the end of the PROC. Do not edit the LOG for PROC SQL in any way.

Re: PROC SQL: CASE WHEN statement ERROR

Kurt_Bremser — Fri, 17 Jul 2020 16:49:51 GMT

When we give you advice, we expect you to FOLLOW it.

The log shows clearly that you did NOT remove the blanks between the table alias and the variable name.

DO THIS before you run the next test.

Re: PROC SQL: CASE WHEN statement ERROR

sufiya — Fri, 17 Jul 2020 17:02:27 GMT

@Kurt_Bremser , @PaigeMiller , @ballardw , @SASKiwi

Thank you for the help guys! Figured it out, just needed to remove the 'Group By' + tweaked the CASEstatement and it ran with no errors.

PROC SQL;
CREATE TABLE BUCKETS AS 
 
SELECT DISTINCT 
a.MONTH, 
a.YEAR, 
a.ANIMAL, 
a.BAN, 
a.VOLUME, 
a.SIGHTINGS,
(case  when a.SIGHTINGS >= 0  and a.SIGHTINGS <=  49.99 then '1.0-49'   
when a.SIGHTINGS  >= 50   and   a.SIGHTINGS <= 99.99 then '2.50-99'
when a.SIGHTINGS  >= 100 and   a.SIGHTINGS <= 249.99 then '3.100-249'
when a.SIGHTINGS  >= 250 and   a.SIGHTINGS <= 499.99 then '4.250-499'
when a.SIGHTINGS  >= 500 and   a.SIGHTINGS <=999.99 then '5.500-999'
when a. SIGHTINGS >= 1000 then '6.1000+'
else '' end) as Buckets

FROM WORK.MERGE a
;QUIT;

Re: PROC SQL: CASE WHEN statement ERROR

ballardw — Fri, 17 Jul 2020 17:19:01 GMT

You might investigate formats.

You could create a format with the desired ranges and use that for almost any purpose that requires "binning" a single variable in analysis, reporting or graphing procedures.

Formats have the flexibility of working with multiple variables with similar coding ranges, do not require adding variables if you want to change the ranges, or have multiple ranged defined simultaneously and use the desired one as situation demands and the logic for ranges in proc format often makes it much easier than if/then/else. And for specific procedures that support them, Tabulate, Report, Summary and Means, Multilabel formats allow overlapping ranges generating summaries for each defined range (which would require multiple variables otherwise).