Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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,610

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;

View solution in original post


All Replies
Super User
Posts: 6,534

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

[ Edited ]

How about adding to the same DATA step:

 

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

Posted in reply to Astounding

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,534

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
Super User
Posts: 9,194

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: 9,556

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: 9,556

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 ]
Posted in reply to KurtBremser

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: 9,556

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

Posted in reply to KurtBremser

sorry i forgot the last variable Smiley Happy

Super User
Posts: 9,556

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


raafat wrote:

sorry i forgot the last variable Smiley Happy


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,610

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,610

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 Smiley Happy.  Is possible to delete the column of the first max after finding it, then find the second max simultaneously? 

☑ This topic is solved.

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

Discussion stats
  • 17 replies
  • 253 views
  • 2 likes
  • 5 in conversation