Add the index of the column with the maximum value as a new column
My question is simple. When the details are below,
var1 var2 var3
10 40 60
15 10 5
I want to add a new column MaxValueVar
that returns the index of the column that has the maximum value among var1
, var2
and var3
. That is, I want to make a table like 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?
source to share
One solution for your reference according to the sample you provide here. You didn't mention how to deal with connections. This is where the first occurrence appears for links.
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;
source to share
This is similar to Robbie's solution. It uses a function vname()
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;
source to share
The code below should work as expected and also create more columns if there are relationships. I know what you said not to worry, but it hurt me!
Test input 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 for checking 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;
The result looks like this:
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 .
source to share