BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mdz
Fluorite | Level 6 mdz
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

10 REPLIES 10
PaigeMiller
Diamond | Level 26

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
mdz
Fluorite | Level 6 mdz
Fluorite | Level 6

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

Kurt_Bremser
Super User

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;
PaigeMiller
Diamond | Level 26

@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
SuryaKiran
Meteorite | Level 14

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
FreelanceReinh
Jade | Level 19

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.

novinosrin
Tourmaline | Level 20

@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;
FreelanceReinh
Jade | Level 19

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

novinosrin
Tourmaline | Level 20

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. 

SuryaKiran
Meteorite | Level 14

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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