topic Re: Comparing multiple columns using if-statements in SAS Programming

Comparing multiple columns using if-statements

jhsaunde — Sat, 25 Mar 2017 10:42:25 GMT

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!

Re: Comparing multiple columns using if-statements

Tom — Sat, 25 Mar 2017 12:28:03 GMT

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;

Re: Comparing multiple columns using if-statements

rogerjdeangelis — Sat, 25 Mar 2017 13:14:14 GMT

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

Re: Comparing multiple columns using if-statements

rogerjdeangelis — Sat, 25 Mar 2017 13:15:40 GMT

You can also do this

data want;
retain MaxPos;
set have;
array x[5];
MaxPos = whichn(max(of x[*]),of x[*]);
run;