## Finding the second max if the data contains two identical max values

Solved
Occasional Contributor
Posts: 12

# Finding the second max if the data contains two identical max values

[ Edited ]

Hi guyes. I have a problem with finding the second max when i have two identical max values. The code below gives the same max value and colum name, wich is 5 and col3. Is it possible to get the second max as 5 and col4 instead of col3?

data want;

array col(5) (1 4 5 5 4);

index_of_1nd=whichn(largest(1,of col(*)),of col(*));

value_1=vname(col(index_of_1nd));

index_of_2nd=whichn(largest(2,of col(*)),of col(*));

value_2=vname(col(index_of_2nd));

run;

Accepted Solutions
Solution
‎01-29-2018 08:26 AM
Super User
Posts: 10,849

## Re: Finding the second max if the data contains two identical max values

Actually , It could be rewrited by WHICHN().

I think it is more readable for you .

``````
data want;

array col(5) (1 4 5 5 4);

temp=max(of col(*));
idx=whichn(temp,of col(*));
col{idx}=.;
index_of_1nd=idx;
value_1=vname(col(index_of_1nd));

temp=max(of col(*));
idx=whichn(temp,of col(*));
col{idx}=.;
index_of_2nd=idx;
value_2=vname(col(index_of_2nd));

drop temp idx;
run;``````

All Replies
Super User
Posts: 6,931

## Re: Finding the second max if the data contains two identical max values

[ Edited ]

if value_1=value_2 then do;

index_of_2nd=whichn(largest(3,of col(*)),of col(*));

value_2=vname(col(index_of_2nd));

end;

Of course, it's possible that there are three values tied for the max.  So you could expand that idea:

do k=2 to dim(col) until (value_1 ne value_2);

index_of_2nd=whichn(largest(k,of col(*)), of col(*));

value_2 = vname(col(index_of_2nd));

end;

Occasional Contributor
Posts: 12

## Re: Finding the second max if the data contains two identical max values

that returns col3 and value of 3 for the first max and the second max is col2 and value 2 which i do not understand. what i am interested in is the the first max value=5 and col3 and the second max value 5 and col4

Super User
Posts: 6,931

## Re: Finding the second max if the data contains two identical max values

Sorry, that can happen before coffee in the morning.  Here's a better way:

data want;

array col(5) (1 4 5 5 4);

array copy (5) _temporary_;

do k=1 to 5;

copy{k} = col{k};

end;

index_of_1nd=whichn(largest(1,of copy(*)),of copy(*));

value_1=vname(col(index_of_1nd));

copy{index_of_1nd} = .;

index_of_2nd=whichn(largest(1,of copy(*)),of copy(*));

value_2=vname(col(index_of_2nd));

run;

Super User
Posts: 9,829

## Re: Finding the second max if the data contains two identical max values

Its easy if you keep your data in normalised format, i.e. going down the page rather than across.  Simple sort distinct, and take second observation per group.   Transposed data is just harder to work with in all senses.

Super User
Posts: 10,557

## Re: Finding the second max if the data contains two identical max values

First transpose your dataset (always a win), and then do

``````proc sql;
create table want as
select
groupval,
your_value,
otherval
from transposed
group by groupval
having your_value = max(your_value)
;
quit;``````
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,557

## Re: Finding the second max if the data contains two identical max values

Applied to your original data, the process looks like this:

``````data have;
array col(5) (1 4 5 5 4);
run;

proc transpose data=have out=transposed;
var col:;
run;

proc sql;
create table want as
select
_name_,
col1
from transposed
having col1 = max(col1)
;
quit;
``````

See how simple the code becomes, once you turn dumb data into intelligent data (Maxim 33)?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

## Re: Finding the second max if the data contains two identical max values

[ Edited ]

i cant not apply your solution kurtbremser as the data updates every week, which means that I get a unique max value sometimes and sometimes identical max value. Furthermore the example i posted is to simple. The real data looks almost like,

DATA want;
INPUT NAMES \$ TEAM \$ sa qu comp qa;
DATALINES;
Ab alfa 5 3 4 5
Bb beta 6 7 2 7
cb sigma 3 4 6 7
RUN;

which make it harder to transpose.

Super User
Posts: 10,557

## Re: Finding the second max if the data contains two identical max values

[ Edited ]

raafat wrote:

i cant not apply your solution kurtbremser as the data updates every week, which means that I get a unique max value sometimes and sometimes identical max value. Furthermore the example i posted is to simple. The real data looks almost like,

DATA want;
INPUT NAMES \$ TEAM \$ sa qu comp;
DATALINES;
Ab alfa 5 3 4 5
Bb beta 6 7 2 7
cb sigma 3 4 6 7
RUN;

which make it harder to transpose.

Heh?

``````DATA have;
INPUT NAMES \$ TEAM \$ sa qu comp;
DATALINES;
Ab alfa 5 3 4 5
Bb beta 6 7 2 7
cb sigma 3 4 6 7
;
RUN;

proc transpose data=have out=transposed;
by names team;
var sa qu comp;
run;

proc sql;
create table want as
select names, team, _name_, col1
from transposed
group by names, team
having col1 = max(col1)
;
quit;``````

Edit: added semicolon-only line in the first data step to end the datalines section.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 12

## Re: Finding the second max if the data contains two identical max values

sorry i forgot the last variable

Super User
Posts: 10,557

## Re: Finding the second max if the data contains two identical max values

raafat wrote:

sorry i forgot the last variable

No problem, just add it to the var statement in the proc transpose.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,849

## Re: Finding the second max if the data contains two identical max values

Reverse the order of vaiables.

``index_of_1nd=whichn(largest(1,of col(*)),of col5-col1);``
Occasional Contributor
Posts: 12

## Re: Finding the second max if the data contains two identical max values

can not as the the column names are different  for example  (sale,compliance....)

Super User
Posts: 10,849

## Re: Finding the second max if the data contains two identical max values

``````data want;

array col(5) (1 4 5 5 4);
temp=largest(1,of col(*));
do i=1 to dim(col);
if col{i}=temp then do;col{i}=.;index_of_1nd=i;end;
end;
value_1=vname(col(index_of_1nd));

temp=largest(2,of col(*));
do i=1 to dim(col);
if col{i}=temp then index_of_2nd=i;
end;
value_2=vname(col(index_of_2nd));

drop i temp;
run;``````
Occasional Contributor
Posts: 12

## Re: Finding the second max if the data contains two identical max values

KSHARP: i do not get the desired output, which is max=5 and col3, max2=5 col4 .  Is possible to delete the column of the first max after finding it, then find the second max simultaneously?

☑ This topic is solved.