Hi,
I am trying to convert this if/then statements into nested case when statements. I am using Proc SLQ as supposed to sas datastep because I want to do group by and sum functions. I understand that datasteps can perform similar task but I want to use proc sql for this particular job. My code looks like below
Data temp;
Length Rank_rng $7.;
Set population_a;
If city_code = 100 then
do;
If Rank_100 in (1,2,3) then Rank_rng = '1_3';
else if Rank_100 in (4,5,6) then Rank_rng = '4_6';
else if Rank_100 in (7,8,9) then Rank_rng = '7_9';
else Rank_rng = 'NO_Rank';
end;
else If city_code = 101 then
do;
If Rank_101 in (1,2,3) then Rank_rng = '1_3';
else if Rank_101 in (4,5,6,7,8) then Rank_rng = '4_8;
else if Rank_101 in (13,14,15) then Rank_rng = 13_15;
else Rank_rng = 'NO_Rank';
end;
else If city_code = 102 then
do;
If Rank_102 in (20,21,22) then Rank_rng = 20_22;
else if Rank_102 in (23,24,25,26) then Rank_rng = 23_26;
else if Rank_102 in (27,28,29,30) then Rank_rng = 27_30;
else Rank_rng = 'NO_Rank';
end;
Run;
Can someone help me to put the above if/then statements into Proc sql nested case when statements?
Thanks in advance for your help.
I may have read your question wrong as my code differs from that of my colleagues, but is close to Linlin's code. i.e.:
data population_a;
input city_code rank_100-rank_102;
cards;
100 1 . .
100 5 . .
100 8 . .
101 . 1 .
101 . 5 .
101 . 14 .
102 . . 21
102 . . 25
102 . . 29
;
proc sql;
create table want as
select *,
case city_code
when 100 then case
when Rank_100 between 1 and 3 then '1_3'
when Rank_100 between 4 and 6 then '4_6'
when Rank_100 between 7 and 9 then '7_9'
else 'Others'
end
when 101 then case
when Rank_101 between 1 and 3 then '1_3'
when Rank_101 between 4 and 8 then '4_8'
when Rank_101 between 13 and 15 then '13_15'
else 'Others'
end
when 102 then case
when Rank_102 between 20 and 22 then '20_22'
when Rank_102 between 23 and 26 then '23_26'
when Rank_102 between 27 and 30 then '27_30'
else 'Others'
end
else 'Others'
end as Rank_rng length=7
from population_a
;
quit;
I don't know about nested case-when. The following code seems to be equvalent to your datastep code.
proc sql;
create table as
select *,
case
when city_code = 100 and Rank_100 in (1,2,3)
then '1_3'
when city_code = 100 and Rank_100 in (4,5,6)
then '4_6'
when city_code = 100 and Rank_100 in (7,8,9)
then '7_9'
when city_code = 100 and Rank_100 not in (1,2,3,4,5,6,7,8,9)
then 'NO_Rank'
when city_code = 101 and Rank_100 in (1,2,3)
then '1_3'
when city_code = 101 and Rank_100 in (4,5,6,7,8)
then '4_8'
when city_code = 101 and Rank_100 in (13,14,15)
then '13_15'
when city_code = 101 and Rank_100 not in (1,2,3,4,5,6,7,8,13,14,15)
then 'NO_Rank'
when city_code = 102 and Rank_100 in (20,21,22)
then '20_22'
when city_code = 102 and Rank_100 in (23,24,25,26)
then '23_26'
when city_code = 102 and Rank_100 in (27,28,29,30)
then '27_30'
when city_code = 102 and Rank_100 not in (20,21,22,23,24,25,26,27,28,29,30)
then 'NO_Rank'
else 'Others'
end as Rank_rng length=7
from population_a
;
quit;
Update: a quick search showing nested case-when is actually supported:
http://www.nesug.org/Proceedings/nesug97/posters/zhang.pdf
Good Luck,
Haikuo
how about:
data population_a;
input city_code rank_100;
cards;
100 1
100 2
100 4
100 8
101 3
101 6
101 9
102 21
102 26
102 30
;
proc sql;
create table temp as
select city_code,rank_100,
case
when city_code=100 then
case
when rank_100 in (1,2,3) then '1_3'
when rank_100 in (4,5,6) then '4_6'
when rank_100 in (7,8,9) then '7-9'
else 'no_rank' end
when city_code=101 then
case
when rank_100 in (1,2,3) then '1_3'
when rank_100 in (4,5,6,7,8) then '4_8'
when rank_100 in (13,14,15) then '13-15'
else 'no_rank' end
else
case
when rank_100 in (20,21,22) then '20_22'
when rank_100 in (23,24,25,26) then '23_26'
when rank_100 in (27,28,29,30) then '27_30'
else 'no_rank' end
end as Rank_rng format=$7.
from population_a;
quit;
proc print;run;
Obs code rank_100 Rank_rng
1 100 1 1_3
2 100 2 1_3
3 100 4 4_6
4 100 8 7-9
5 101 3 1_3
6 101 6 4_8
7 101 9 no_rank
8 102 21 20_22
9 102 26 23_26
10 102 30 27_30
Linlin
I may have read your question wrong as my code differs from that of my colleagues, but is close to Linlin's code. i.e.:
data population_a;
input city_code rank_100-rank_102;
cards;
100 1 . .
100 5 . .
100 8 . .
101 . 1 .
101 . 5 .
101 . 14 .
102 . . 21
102 . . 25
102 . . 29
;
proc sql;
create table want as
select *,
case city_code
when 100 then case
when Rank_100 between 1 and 3 then '1_3'
when Rank_100 between 4 and 6 then '4_6'
when Rank_100 between 7 and 9 then '7_9'
else 'Others'
end
when 101 then case
when Rank_101 between 1 and 3 then '1_3'
when Rank_101 between 4 and 8 then '4_8'
when Rank_101 between 13 and 15 then '13_15'
else 'Others'
end
when 102 then case
when Rank_102 between 20 and 22 then '20_22'
when Rank_102 between 23 and 26 then '23_26'
when Rank_102 between 27 and 30 then '27_30'
else 'Others'
end
else 'Others'
end as Rank_rng length=7
from population_a
;
quit;
All the 3 answers were helpful. Thanks a lot to all of you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.