Maximum value related question

[superbug]

As in the attached data, I used the following code to find max value of each row

data flag1; set flag;
maxvalue=MAX(Score,Response,Min_Var,Liklihood,Latency,Speedness,Bimodal);
run;

Then I want to generate a column called "index" to indicate which variable does this max value associated with. 

Which SAS function should I use? Please help.

Thanks!

[Duggins · Accepted solution]

Assuming your maximum is unique, so that the value of Index is unique, you could do the following. By placing your variables in an array, once you identify the maximum, you can loop through the array and identify where the match occurs and use the VNAME function to look up the variable name.

If your maximum may not be unique, this code can be modified to find all maxima and their indices.

data want;
  set have;
  array MyList[*] score response min_var likelihood latency Speedness Bimodal;
  maxValue = max(of MyList[*]);
  do i = 1 to dim(myList);
    if MyList[i] = maxValue then Index = vname(MyList[i]);
  end;
run;

[superbug]

@Duggins 

Thanks much! Your code worked!

[PaigeMiller]

You could use an array

data flag1; 
set flag;
maxvalue=MAX(Score,Response,Min_Var,Liklihood,Latency,Speedness,Bimodal);
array x score response min_var liklihood latency speedness bimodal;
do i=1 to dim(x);
     if x(i)=maxvalue then do;
        index=i;
        leave;
     end;
end;
drop i;
run;

[superbug]

@PaigeMiller 

Thank you so much!

Your code also worked.

[ballardw]

The WHICHN and WHICHC functions return the position number of a value given a list of variables or values. If there are multiple variables with the same value the function will return the position of the first encountered from left to right in the value order. The first parameter of the function call is the value to find followed by the variables or values to search.

data flag1; 
   set flag;
   maxvalue=MAX(Score,Response,Min_Var,Liklihood,Latency,Speedness,Bimodal);
   array x score response min_var liklihood latency speedness bimodal;
   /*index as character with value = name of variable*/
   indexc= vname(x[whichn(maxvalue,of x(*))]);
   /* index as position number of value*/
   index= whichn(maxvalue,of x(*));

run;

You don't indicate what your index value should contain though.

[PaigeMiller]

I always forget about WHICHN! 😞

@ballardw wouldn't you need a length statement for indexc ?

[ballardw]

---

@PaigeMiller wrote:

I always forget about WHICHN! 😞

@ballardw wouldn't you need a length statement for indexc ?

---

The VNAME function defaults to  200 characters returned. So probably a good idea but not needed to prevent truncation, which I assume is the concern.

[Duggins]

That's a great addition, @ballardw - it would nicely remove that loop from my code and (probably?) reduce computational time. 

[ballardw]

---

@Duggins wrote:

That's a great addition, @ballardw - it would nicely remove that loop from my code and (probably?) reduce computational time. 

---

I would actually test before assuming computation time is significantly impacted. The nice thing about the WHICHN with an array is you can make the code parallel and maintainable by using the same "of array(*)" syntax in both the call to the Mean (or other similar functions). Then if latter you need to add other variables the change only needs to be made in the array definition.

One thing to always keep in mind with WHICHN and WHICHC is: Do I need all possible matches or is any one sufficient.

Another nice thing with the which functions is you can provide the order, either in the array or the which function call if you would prefer one variable over another to be selected if both match the criteria.

And for some processes if you have all the related variables with a common stem for the name such as Meas1 to MeasN you can define the array with a list such as

array x Meas: ;

which places all of the variables that start with MEAS into the array (caution with this make sure you want them all). 

Some steps, like calls to Proc Transpose might be creating an unknown number of Col1 to ColN (or your choice of base name) variables and this sort of code makes things somewhat robust.