syntax error proc sql case when statement

[sasprogramming]

I am trying to run the following code and I am running into this error:

proc sql;

create table data_new as select *,

case when a is not null then 500 

else when b>=60 and c=0 then 400

else when d <6 and Days_Arrears>e then 300

else when d <6 and Days_Arrears>e then 200

else when d <6 and Days_Arrears=e then 100

else when f = 'available' then 50

else f = 'vacant' then 25

else 0 end as Points

from data_old;

quit;

Error:

1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc sql;
 74         
 75         create table data_new as select *,
 76         
 77         case when a is not null then 500
 78         
 79         else when b>=60 and c=0 then 400
                      ____________
                      22
                      76
 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, AND, EQ, EQT, 
               GE, GET, GT, GTT, LE, LET, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.  
 
 ERROR 76-322: Syntax error, statement will be ignored.
 

[ed_sas_member]

Hi @sasprogramming 

Does that answer you question? -> just remove the multiple "else" to keep one at the very end.

proc sql;

create table data_new as select *,

case when a is not null then 500 

	 when b>=60 and c=0 then 400

	 when d <6 and Days_Arrears>e then 300

	 when d <6 and Days_Arrears>e then 200

         when d <6 and Days_Arrears=e then 100

	 when f = 'available' then 50

	 when f = 'vacant' then 25

else 0 end as Points

from data_old;

quit;

https://www.lexjansen.com/wuss/2011/coders/Papers_Lafler_K_72492.pdf 

[FreelanceReinh]

Hello @sasprogramming,

@ed_sas_member has replied to your post while I was still typing my answer ...

I was wondering why nobody had replied to your post yet, because syntax errors are among the easiest errors to resolve. Here's why: The SAS documentation is really good, so that a comparison of a piece of SAS code and the syntax shown there quickly reveals the difference which constitutes the syntax error.

In your example, use, e.g., "case when" (without quotes) as the search terms and the first link returned will be:

CASE Expression :: SAS® 9.4 SQL Procedure User’s Guide, Fourth Edition

(which is the correct one [assuming SAS version 9.4] because the error occurred in a PROC SQL step, not in FedSQL etc.).

It says:

Syntax

CASE <case-operand>

WHEN when-condition THEN result-expression

<WHENwhen-conditionTHENresult-expression...>

<ELSEresult-expression>

END

So you see that both your "else when" and "else ... then" constructs are invalid. In fact, the second and all subsequent WHEN clauses in a CASE expression have sort of an "implied ELSE" in front of them, i.e., they apply only if all previous WHEN conditions were not met, as does the result expression in the ELSE clause at the end (if any).

Now the syntax correction is easy: Delete the first five "else" keywords and replace the sixth (followed by "f = 'vacant'") with "when" -- as suggested by @ed_sas_member.

You're not done yet, though. Note that most likely there's (at least) one logical error left: The two WHEN conditions for Points=300 and Points=200 are accidentally (?) identical. As a consequence, the second of the two will never apply (because of the "implied ELSE") and none of the observations in data_new will receive 200 points. Reconsider the criteria for 200 and 300 and correct the WHEN condition(s) accordingly.