## Nested case when stmts.

Solved
Frequent Contributor
Posts: 82

# Nested case when stmts.

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?

Accepted Solutions
Solution
‎03-19-2012 09:12 PM
PROC Star
Posts: 8,169

## Re: Nested case when stmts.

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 Replies
Posts: 3,167

## Re: Nested case when stmts.

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

Super Contributor
Posts: 1,636

## Re: Nested case when stmts.

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

Solution
‎03-19-2012 09:12 PM
PROC Star
Posts: 8,169

## Re: Nested case when stmts.

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;

Frequent Contributor
Posts: 82

## Re: Nested case when stmts.

All the 3 answers were helpful. Thanks a lot to all of you!

🔒 This topic is solved and locked.