topic Re: SQL case when else in SAS Programming

SQL case when else

Batman — Tue, 13 Aug 2024 15:47:06 GMT

Not sure why this isn't working, shouldn't I have "end" in the location of the error?

28 proc sql;
29 create table temp as
30 select contract_number, sum(enrollment) as parent_enrol,
31 case when (calculated parent_enrol lt 50000) then 1
32 else case when parent_enrol le 200000 then 2
33 else case when (calculated parent_enrol gt 200000) then 3
34 else 4
35 end as parent_size
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, END, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~,
~=.

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

36 from D107342.AP15747022_Master
37 group by contract_number;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
38 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time

Re: SQL case when else

Reeza — Tue, 13 Aug 2024 16:14:26 GMT

EDIT: Too many cases and elses here.

28 proc sql;
29 create table temp as
30 select contract_number, sum(enrollment) as parent_enrol,
31 case when (calculated parent_enrol lt 50000) then 1
32 else case when parent_enrol le 200000 then 2
33 else case when (calculated parent_enrol gt 200000) then 3
34 else 4
35 end as parent_size

Correct syntax

28 proc sql;
29 create table temp as
30 select contract_number, sum(enrollment) as parent_enrol,
31 case when (calculated parent_enrol lt 50000) then 1
32   when parent_enrol le 200000 then 2
33  when (calculated parent_enrol gt 200000) then 3
34 else 4
35 end as parent_size

@Batman wrote:

Not sure why this isn't working, shouldn't I have "end" in the location of the error?

28 proc sql;
29 create table temp as
30 select contract_number, sum(enrollment) as parent_enrol,
31 case when (calculated parent_enrol lt 50000) then 1
32 else case when parent_enrol le 200000 then 2
33 else case when (calculated parent_enrol gt 200000) then 3
34 else 4
35 end as parent_size
__
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, END, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~,
~=.

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

36 from D107342.AP15747022_Master
37 group by contract_number;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
38 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time

Re: SQL case when else

Batman — Tue, 13 Aug 2024 16:00:09 GMT

Still getting an error even with your modification

27 proc sql;
28 create table temp as
29 select contract_number, sum(enrollment) as parent_enrol,
30 case when calculated parent_enrol lt 50000 then 1
31 else when calculated parent_enrol le 200000 then 2
__________
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, END, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~,
~=.

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

32 else when calculated parent_enrol gt 200000 then 3
33 else 4
34 end as parent_size
35 from D107342.AP15747022_Master
36 group by contract_number;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
37 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time

Re: SQL case when else

Reeza — Tue, 13 Aug 2024 16:03:19 GMT

No ELSE as well except on last one.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/sqlproc/n0a85s0ijz65irn1h3jtariooea5.htm