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
