Comparing multiple columns using if-statements

Reply
Frequent Learner
Posts: 1

Comparing multiple columns using if-statements

Hello!

 

My dataframe consists of many rows and 25 columns sequentially labeled 'diagx1' to 'diagx25'. Each row represents an individual presenting with one or more health conditions (diagx). There are many different conditions (10,000+), though some are more common than others. Multiple individuals can have the same condition.

 

I wish to narrow my dataframe down to 20 conditions: if any individual has one of these conditions in 'diagx1' to 'diagx25', I want them to remain in my dataframe. I have considered using multiple if statements, though I was wondering if someone had a more elegant solution.

 

 For example, say the 10 conditions were: NN1, NN2, NN3, QQ1, QQ2, QQ3, OO1, OO2, OO3, V1, V2

 

data hosp_prac;

set hosp;

 if diagx1 = NN1 or diagx1 = NN2 or diagx1 = NN3...

    or diagx2 = NN1 or diagx2 = NN2 or diagx2 = NN3...

    or diagx3 = NN1 or diagx3 = NN2...

         ....then output;

             run;

 

Any help would be greatly appreciated! I hope its clear enough - this is my first time posting - I'm not even sure if this belongs in this location.

 

Thanks!

Super User
Super User
Posts: 7,039

Re: Comparing multiple columns using if-statements

[ Edited ]

You could use an array to make the coding easier.

 

data hosp_prac;
  set hosp;
  array dx diagx1 - diagx25 ;
  found=0;
  do i=1 to dim(dx) while (found=0) ;
    found = dx(i) in ('NN1','NN2', ...... ) ;
  end;
  if found ;
run;
Valued Guide
Posts: 505

Re: Comparing multiple columns using if-statements

I like the previous post better, but this is interesting

* create one ob where diag1-diag30 = NN1-NN30
data have;
  array diags[30] $3 diag1-diag30 (%macro gg;%do i=1 %to 30; "NN&i" %end;%mend;%gg);
run;quit;

* check for NN;
data want;
  set have;
  if index(catx(' ',of diag:),'NN');
run;quit;

1 ob written

* if you want  the postion of NN5;
data want;
  set have;
  array diags[30] $3 diag1-diag30 ;
  position = whichc('NN5',of diags[*]);
  put position;
run;quit;

5

Valued Guide
Posts: 505

Re: Comparing multiple columns using if-statements

Posted in reply to rogerjdeangelis
You can also do this

data want;
retain MaxPos;
set have;
array x[5];
MaxPos = whichn(max(of x[*]),of x[*]);
run;
Ask a Question
Discussion stats
  • 3 replies
  • 179 views
  • 1 like
  • 3 in conversation