Nested case when stmts.

[vicky07]

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.

[Haikuo]

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

[Linlin]

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

[art297 · Accepted solution]

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;

[vicky07]

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