topic Re: Finding the second max if the data contains two identical max values in SAS Enterprise Guide

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

raafat — Wed, 24 Jan 2018 11:10:01 GMT

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;

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

Astounding — Wed, 24 Jan 2018 12:03:59 GMT

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;

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

RW9 — Wed, 24 Jan 2018 11:42:36 GMT

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.

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

raafat — Wed, 24 Jan 2018 11:43:27 GMT

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

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

Kurt_Bremser — Wed, 24 Jan 2018 11:48:27 GMT

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;

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

Kurt_Bremser — Wed, 24 Jan 2018 11:53:23 GMT

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)?

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

raafat — Wed, 24 Jan 2018 13:00:00 GMT

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.

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

Ksharp — Wed, 24 Jan 2018 12:22:51 GMT

Reverse the order of vaiables.

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

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

raafat — Wed, 24 Jan 2018 12:25:16 GMT

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

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

Kurt_Bremser — Wed, 24 Jan 2018 13:10:34 GMT

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

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

Ksharp — Wed, 24 Jan 2018 12:59:48 GMT

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;

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

raafat — Wed, 24 Jan 2018 13:00:27 GMT

sorry i forgot the last variable 🙂

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

Kurt_Bremser — Wed, 24 Jan 2018 13:22:02 GMT

@raafat wrote:

sorry i forgot the last variable 🙂

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

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

Astounding — Wed, 24 Jan 2018 13:52:13 GMT

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;

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

raafat — Wed, 24 Jan 2018 14:30:27 GMT

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?

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

Ksharp — Thu, 25 Jan 2018 12:46:51 GMT

NOTICE: code changed for fixing a problem. Sorry !

OK. I misunderstood.

data want;

array col(5) (1 4 5 5 4);
temp1=largest(1,of col(*));

do i=1 to dim(col);
  if col{i}=temp1 then do;
    col{i}=.;index_of_1nd=i;
    value_1=vname(col(index_of_1nd));
	leave;
  end;
end;

temp2=largest(1,of col(*));
do i=1 to dim(col);
  if col{i}=temp2 then do;
   index_of_2nd=i;
   value_2=vname(col(index_of_2nd));
   leave;   /*<--------*/
  end;
end;
drop i temp1 temp2;
run;

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

raafat — Mon, 29 Jan 2018 11:42:09 GMT

KSsharp: it works ! thank you very much. Can you please briefly explain you code, of course if you have time 🙂

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

Ksharp — Mon, 29 Jan 2018 12:18:58 GMT

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;