topic Re: Nested case when stmts. in SAS Procedures

Nested case when stmts.

vicky07 — Mon, 19 Mar 2012 23:47:30 GMT

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.

Re: Nested case when stmts.

Haikuo — Tue, 20 Mar 2012 00:25:30 GMT

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

Re: Nested case when stmts.

Linlin — Tue, 20 Mar 2012 00:53:47 GMT

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

Re: Nested case when stmts.

art297 — Tue, 20 Mar 2012 01:12:59 GMT

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;

Re: Nested case when stmts.

vicky07 — Tue, 20 Mar 2012 01:42:22 GMT

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