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.
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;
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;
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".
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;
@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.
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;
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:
of the group of variables sharing the same (maximum) value.
@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;
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. :-)]
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.