Iterative Case Statement

Reply
New Contributor
Posts: 4

Iterative Case Statement

Hello  All,

I have a question regarding a case statement and iterative processing.  Here is my scenario.  I’m querying a table via proc sql where there is a category column with values (CAT1-CAT35) which correspond to and value in a sub category column(s) (SUB1-SUB35).  Below is simplistic sample of how the data looks in the table.

Category

Sub1

Sub2

Sub3

Sub4

Sub 5

SubN

Cat1

SC1A

Cat2

SC2B

Cat3

SC3C

Cat4

SC4D

Cat5

SC5E

Cat35

SC35X

Category column is always populated and will always be (CAT1 - CAT35)

I can use a case statement that will get me what I want but I’d rather not have 35 conditions if I can avoid it.  

case when COM.CATEGORY ="CAT1" then SUB1

      when COM.CATEGORY ="CAT2" then SUB2

      when COM.CATEGORY ="CAT3" then SUB3

      when COM.CATEGORY ="CAT4" then SUB4

      when COM.CATEGORY ="CAT5" then SUB5

      when COM.CATEGORY ="CAT6" then SUB6

      when COM.CATEGORY ="CAT7" then SUB7

   ...

    when COM.CATEGORY ="CAT35" then SUB35

      end as SubCategory

Is there a way to loop through this (%Do %To %End)? Any advice would be appreciated.  Thanks!

Chris


New Contributor
Posts: 4

Re: Iterative Case Statement

Well by writing it out I think I got my answer, I am using the below and it is returning the identical results as the long-winded case statement so I think I'm good to go.

,%macro SUBN;

     case %do I = 1 %to 35;

          when COM.CATEGORY="CAT&I" then SUB&I %end;

               %mend SUBN;

            %SUBN

end as SubCategory_M

Thanks!

Super User
Posts: 17,811

Re: Iterative Case Statement

Use an array instead of a macros. Readability is one reason, efficiency is the second.

Get the number from the category (using the compress function) and then find the appropriate index value from the subcategory array.

Something like the following:

data want;

set have;

array sub(*) sub1 - sub35;

*get the number from the category variable;

number=input(compress(category, 'kd'), 8.);

sub_category_m = sub(number);

run;

Super User
Posts: 9,676

Re: Iterative Case Statement

data have;
input (Category sub1 sub2) ($);
cards;
cat1 1a .
cat2 . 2b
;
run;
/* if you have only one SUB value in a row,then coalescec()*/
data have;
 set have;
 subcat=coalescec(of sub1-sub2);
run;

/*if you don't have only one SUB value ,then ARRAY*/

data have;
 set have;
 array _s{*} $ sub1-sub2 ;
 subcat=_s{_n_};
run;




Xia Keshan

Super User
Super User
Posts: 6,499

Re: Iterative Case Statement

Why use PROC SQL?  Just query it using a DATA step instead.

New Contributor
Posts: 4

Re: Iterative Case Statement

Hello Tom,

It’s an existing query in PROC SQL that I’m modifying.  If PROC SQL becomes too prohibitive then I’ll move into data step, but for now I’m trying to keep the current format.

Thanks,

Chris

New Contributor
Posts: 4

Re: Iterative Case Statement

Thank you all for the quick responses, they were very helpful!

Ask a Question
Discussion stats
  • 6 replies
  • 281 views
  • 6 likes
  • 4 in conversation