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?
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;
if maxvaluevar = 0 and maxvalue = vars(i) then maxvaluevar = i;
Chris J 2012-04-04 09:37
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;
leave
it would process the whole array and the last match would be returned - cmjohns 2012-04-10 21:02
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 .