Proc SQL Case When Syntax Error

theponcer — Tue, 18 Jun 2019 19:59:53 GMT

I am trying to use a nested case statement in Proc Sql to define a new variable. I am able to do this using a data step using Nested If's in the code below, but my Proc Sql code throws a syntax error. The data step and proc sql outputs should match. What am I doing wrong?


*Test Data Set;
data test;                     
   input Category_1 $ Category_2 $ Category_3 $ Category_4 $;       
   datalines;           
Y N  A 1
Y N  A 5
Y N  B 6
Y Y  B 3
Y Y  A 2
Y Y  A 5
Y Y  B 1
Y Y  B 4
N Y  A 2
N Y  A 5
N Y  B 3
N Y  B 6
N N  A 1
N N  A 4
N N  B 3
N N  B 5
N Y  A 1
N Y  A 6
N Y  B 3
N Y  B 4
;  

*Data step;
data test_data;
set test;
length Field $4;
if Category_1= "N" then do;
	if Category_2= "N" then do;
		if Category_3= "A" then Field= "AB";
		else if Category_4 in (1,2,3) then Field= "ABC";
		else if Category_4 in (4,5,6) then Field= "ABCD";
	end;
	else if Category_2 = "Y" then do;
		if Category_3= "A" then Field= "AC";
		else if Category_3= "B" then Field= "ACB";
	end;
end;
else if Category_1= "Y" then do;
	if Category_2= "N" then do;
		if Category_3= "A" then Field= "BA";
		else if Category_4 in (1,2,3) then Field= "BAC";
		else if Category_4 in (4,5,6) then Field= "BACD";
	end;
	else if Category_2= "Y" then do;
		if Category_3= "A" then Field= "BAD";
		else if Category_3= "B" then Field= "CAD";
	end;
end;
run;

*Proc Sql;
proc sql; create table test_sql as select *,
case 
	when Category_1="N" 
		then
			case 
				when Category_2="N" 
					then
						case 
							when Category_3="A" 
								then "AB"
 							else when Category_4 in(1,2,3) 
								then "ABC"
							
						end
							else when Category_4 in (4,5,6) 
								then "ABCD"
							
						end 
				else when Category_2="Y"
					then 
						case 
							when Category_3="A" 
								then "AC"
							else when  Category_3="B"
								then "ACB"
						end
				end
			end
	else when Category_1="Y"
		then 
			case 
				when Category_2="N" 
					then
						case 
							when Category_3="A" 
								then "BA"
 							else when Category_4 in(1,2,3) 
								then "BAC" 
							else when Category_4 in (4,5,6) 
								then "BACD"
						end 
				else when Category_2="Y"
					then 
						case 
							when Category_3="A" 
								then "BAD"
							else when  Category_3="B"
								then "CAD"
						end
			end
end as Field

from test;
quit;

Log:

proc sql;
24 ! create table test_sql as select *,
25 case
26 when Category_1="N"
27 then
28 case
29 when Category_2="N"
30 then
31 case
32 when Category_3="A"
33 then "AB"
34 else when Category_4 in(1,2,3)
__________
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, -, '.', /, AND, OR, |, ||.
ERROR 76-322: Syntax error, statement will be ignored.
35 then "ABC"
36
37 end
38 else when Category_4 in (4,5,6)
39 then "ABCD"
40
41 end
42 else when Category_2="Y"
43 then
44 case
45 when Category_3="A"
2 The SAS System 12:31 Tuesday, June 18, 2019
46 then "AC"
47 else when Category_3="B"
48 then "ACB"
49 end
50 end
51 end
52 else when Category_1="Y"
53 then
54 case
55 when Category_2="N"
56 then
57 case
58 when Category_3="A"
59 then "BA"
60 else when Category_4 in(1,2,3)
61 then "BAC"
62 else when Category_4 in (4,5,6)
63 then "BACD"
64 end
65 else when Category_2="Y"
66 then
67 case
68 when Category_3="A"
69 then "BAD"
70 else when Category_3="B"
71 then "CAD"
72 end
73 end
74 end as Field
75
76 from test;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
77 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 59.62k
OS Memory 27556.00k
Timestamp 06/18/2019 02:59:21 PM
Step Count 50 Switch Count 0
Page Faults 0
Page Reclaims 13
Page Swaps 0
Voluntary Context Switches 3
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

Re: Proc SQL Case When Syntax Error

Reeza — Tue, 18 Jun 2019 20:04:04 GMT

I would highly recommend a look up table or a format instead of this type of logic.

Re: Proc SQL Case When Syntax Error

SASKiwi — Tue, 18 Jun 2019 20:21:56 GMT

IMO trying to reproduce convoluted conditional logic in SQL at all is what you are doing wrong. Choose the right tool for the job - your DATA step version is way better.

Re: Proc SQL Case When Syntax Error

PGStats — Tue, 18 Jun 2019 21:10:44 GMT

The case expression in SQL is

case

when <expression> value

...

else value

end as name

there is no else when.

Remove the else.

topic Re: Proc SQL Case When Syntax Error in SAS Programming

Proc SQL Case When Syntax Error

Re: Proc SQL Case When Syntax Error

Re: Proc SQL Case When Syntax Error

Re: Proc SQL Case When Syntax Error