topic Re: Determining which variable produced the maximum value in SAS Programming

Determining which variable produced the maximum value

mdz — Tue, 26 Jun 2018 12:39:11 GMT

I have 30 numeric variables (named VALUE1, VALUE2, VALUE3, …) and have already written to code to find which has the Maximum value, however, I also need to know which of the 30 numeric variables produced that maximum value. Please see the example code below (this is what I have so far, but I don't know what else I need to get my desired output).

data example1;
set example;

MAX_Value= max( VALUE1, VALUE2, VALUE3, …, VALUE29, VALUE30);
run;

Basically I just need to know for a given observation if the maximum value is 1000 which of the 30 values did that come from.

Re: Determining which variable produced the maximum value

PaigeMiller — Tue, 26 Jun 2018 12:46:20 GMT

UNTESTED CODE

proc summary data=have;
    var value1-value30;
    output out=max max=max1-max30;
run;
data want;
    set max;
    array m max1-max30;
    maxvaluevar = whichn(max(of max1-max30),of m[*]);
run;

Re: Determining which variable produced the maximum value

mdz — Tue, 26 Jun 2018 12:59:48 GMT

After running this code, I realized I may need to add clarity to my goal. It appears this would work to determine across all the observations of Value1 what is the maximum, but that is not my goal.

I am not looking for the maximum for each Value, but instead for a given observation I am trying to find the maximum across the 30 values. For example, for observation 1 I need to know which of the 30 values is the highest (I have already figured out how to code that) and which of the 30 values that maximum came from. Ideally each observation would have a variable named "Max_value" and "Max_variable".

Re: Determining which variable produced the maximum value

SuryaKiran — Tue, 26 Jun 2018 13:05:35 GMT

Here is another way. I learned about VNAME from @SASJedi Jedi SAS Tricks

data test;
input value1-value10;
datalines ;
1 2 3 4 20 6 7 8 9 10
;
run;

data want;
set test;
array values (10)value1-value10;
max_value=max(of value1-value10);
do i=1 to dim(values);
if values(i)=max_value then var=vname(values(i));
end;
run;

Re: Determining which variable produced the maximum value

Kurt_Bremser — Tue, 26 Jun 2018 13:26:39 GMT

An alternative works by transposing the dataset and determining the row with the highest value:

data test;
input id $ value1-value10;
datalines ;
A 1 2 3 4 20 6 7 8 9 10
;
run;

proc transpose data=test out=int;
by id;
var value:;
run;

proc sql;
select id, _name_, col1
from int
group by id
having col1 = max(col1);
quit;

Re: Determining which variable produced the maximum value

PaigeMiller — Tue, 26 Jun 2018 13:30:50 GMT

@mdz wrote:

After running this code, I realized I may need to add clarity to my goal. It appears this would work to determine across all the observations of Value1 what is the maximum, but that is not my goal.

I am not looking for the maximum for each Value, but instead for a given observation I am trying to find the maximum across the 30 values. For example, for observation 1 I need to know which of the 30 values is the highest (I have already figured out how to code that) and which of the 30 values that maximum came from. Ideally each observation would have a variable named "Max_value" and "Max_variable".

So run the WHICHN function on each row of your original data.

Re: Determining which variable produced the maximum value

FreelanceReinh — Tue, 26 Jun 2018 14:02:58 GMT

It should be noted that in the case of a tied maximum (i.e. more than one variable having the maximum value) the suggested solutions return different results:

WHICHN returns (the array index of) the first variable
SuryaKiran's solution returns the name of the last variable
KurtBremser's solution returns the names of all variables

of the group of variables sharing the same (maximum) value.

Re: Determining which variable produced the maximum value

novinosrin — Tue, 26 Jun 2018 14:19:59 GMT

@FreelanceReinh Absolutely sir, whichn is the way to go. Loop is not ideal and too costly for this exercise in my opinion

data test;
input value1-value10;
datalines ;
1 2 3 4 20 6 7 8 9 10
;
run;

data want;
set test;
array values (10)value1-value10;
max_value=max(of value1-value10);
var=vname(values(whichn(max_value,of values(*))));
run;

Re: Determining which variable produced the maximum value

SuryaKiran — Tue, 26 Jun 2018 14:30:25 GMT

Agree, In case if multiple values with maximum values then this would list all of the variables.

data want;
Format Var $50.;
set test;
array values (10)value1-value10;
max_value=max(of value1-value10);
do i=1 to dim(values);
if values(i)=max_value then var=CATX('|',strip(var),vname(values(i)));
end;
run;

Re: Determining which variable produced the maximum value

FreelanceReinh — Tue, 26 Jun 2018 14:31:16 GMT

Wouldn't it depend on the purpose of the result (hopefully documented in specifications), which of the three solutions (i.e. ways of handling ties) is "the way to go"?

[But thanks for calling me "sir" -- I know that you mean it as a honorary title. :-)]

Re: Determining which variable produced the maximum value

novinosrin — Tue, 26 Jun 2018 14:36:42 GMT

Hmm interesting as you bring in a new dynamic with "ties". Well well, I guess will have to wait and see how OP would want to deal with that and which variable he/she wants to pick.