Add the index of the column with the maximum value as a new column

Go To StackoverFlow.com

3

My question is simple. When data is as below,

var1    var2    var3
10      40      60
15      10      5

I want to add a new column MaxValueVar that returns index of a column that has maximum value among var1, var2 and var3. That is, I want to make a table as below.

var1    var2    var3    MaxValueVar
10      40      60      3
15      10      5       1

In R I would use:

apply(vector, 1, which.max)

How can I accomplish this using SAS?

2012-04-04 04:29
by TaeHeon Kim
Is your question asking about how to accomplish this in SAS - Dason 2012-04-04 04:52
@Dason Right. Sorry, my question was confusing - TaeHeon Kim 2012-04-04 04:57
What do you want to do with missing data? And how do you want to treat ties for the maximum (i.e. - if you have two instances of 60 in the first row)? These will affect the potential answers a bit - thelatemail 2012-04-04 06:40
@thelatemail You're right. But I do not need to care about missing values or tie values as of now. So if presencce of missing or tie values bothers you, just ignore it. Thanks - TaeHeon Kim 2012-04-04 06:56


4

One Solution for your reference according to the sample you provide here. You didn't mention how to deal with ties. Here for ties, the first occurrence is fetched.

data test;
input var1 var2 var3;
datalines;
10      40      60
15      10      5
run;

data test;
 set test;
 maxvalue=max(of var1-var3);
 maxvaluevar=0;
  array vars (*) var1-var3;
    do i=1 to dim(vars);
     if maxvaluevar=0 then maxvaluevar=i*(maxvalue=vars(i))+maxvaluevar;
    end;
 drop i maxvalue;
run;
2012-04-04 06:42
by Robbie Liu
@RobbieLiu Thank you, RobbieLiu. But I can not understand "i*(maxvalue=vars(i))". Can you explain more - TaeHeon Kim 2012-04-04 07:36
@Robbie_Liu (maxvalue=vars(i)) <- Does this part mean 'If' phrase - TaeHeon Kim 2012-04-04 07:45
It could be re-written as if maxvaluevar = 0 and maxvalue = vars(i) then maxvaluevar = i;Chris J 2012-04-04 09:37
@TaeHeonKim Maxvalue=vars(i) returns 0 or 1 on whether a column equals the maximum value. Then by multiplying i, we get the column number. Chris is right. You may find it is easier to understand - Robbie Liu 2012-04-04 13:54
Thank you very much, both of you - TaeHeon Kim 2012-04-06 00:59


2

Here is a similar solution to that of Robbie's. It uses the vname() function to get the variable name of the first maximum in addition to the index in the array.

data maxarr (drop=i);
input var1 var2 var3;
 array vars(*) var1-var3;
 max=max(of vars(*));
   do i=1 to dim(vars);
     if vars(i)=max then do;
          MaxValueIndx=i;
          MaxValueVar=vname(vars(i));
          leave;
     end;
   end;
datalines;
10      40      60
15      10      5
;
run;

proc print data=maxarr noobs;run;
2012-04-04 16:40
by cmjohns
Thank you, cmjohns. But I have a question. What's the roll of 'leave'? I checked that your code worked appropriately without 'leave' - TaeHeon Kim 2012-04-06 01:03
It just stops the loop from continuing on past where we need it to, selecting the first match it sees. If I did not use leave it would process the whole array and the last match would be returned - cmjohns 2012-04-10 21:02


2

The code below should work as intended, as well as creating more columns in the instance of there being ties. I know you said not to bother, but it was bugging me!

Input test data:

data test;
    input var1 var2 var3;
    cards;
10 40 60
15 10 5
7 8 9
13 13 10
5 7 6
10 11 12
10 10 10
1 3 2
3 3 1
;
run;

Code to check for maximum values:

data test (drop = i howmanymax);
    set test;
    retain howmanymax;

    howmanymax=0;

    array varlist[*] var1--var3;
    array maxnum[3];

    do i = 1 to DIM(varlist);
        if varlist[i] = max(of var1--var3)
            then do;
                howmanymax+1;
                maxnum[howmanymax] = i;
            end;
    end;

run;

Output looks like:

  var1  var2  var3  nummax1 nummax2 nummax3
    10    40    60        3       .       .
    15    10     5        1       .       .
     7     8     9        3       .       .
    13    13    10        1       2       .
     5     7     6        2       .       .
    10    11    12        3       .       .
    10    10    10        1       2       3
     1     3     2        2       .       .
     3     3     1        1       2       .
2012-04-05 00:04
by thelatemail
thank you for your additional help about checking the number of columns that have same maximum values - TaeHeon Kim 2012-04-06 01:14
@TaeHeonKim - No problems, glad to help - don't forget to vote and select an accepted answer (not necessarily mine! - thelatemail 2012-04-06 01:53
Ads