DATA Step, Macro, Functions and more

Determining which variable produced the maximum value

Accepted Solution Solved
Reply
New Contributor mdz
New Contributor
Posts: 4
Accepted Solution

Determining which variable produced the maximum value

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.


Accepted Solutions
Solution
4 weeks ago
Valued Guide
Posts: 597

Re: Determining which variable produced the maximum value

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;
Thanks,
Suryakiran

View solution in original post


All Replies
Respected Advisor
Posts: 3,066

Re: Determining which variable produced the maximum value

[ Edited ]

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;
--
Paige Miller
New Contributor mdz
New Contributor
Posts: 4

Re: Determining which variable produced the maximum value

Posted in reply to PaigeMiller

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".  

Super User
Posts: 10,280

Re: Determining which variable produced the maximum value

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 3,066

Re: Determining which variable produced the maximum value


@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.

--
Paige Miller
Solution
4 weeks ago
Valued Guide
Posts: 597

Re: Determining which variable produced the maximum value

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;
Thanks,
Suryakiran
Trusted Advisor
Posts: 1,259

Re: Determining which variable produced the maximum value

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.

PROC Star
Posts: 1,844

Re: Determining which variable produced the maximum value

Posted in reply to FreelanceReinhard

@FreelanceReinhard 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;
Trusted Advisor
Posts: 1,259

Re: Determining which variable produced the maximum value

Posted in reply to novinosrin

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. :-)]

PROC Star
Posts: 1,844

Re: Determining which variable produced the maximum value

Posted in reply to FreelanceReinhard

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. 

Valued Guide
Posts: 597

Re: Determining which variable produced the maximum value

Posted in reply to FreelanceReinhard

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;
Thanks,
Suryakiran
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 135 views
  • 5 likes
  • 6 in conversation