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

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

17 REPLIES 17
Astounding
PROC Star

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;

 

 

raafat
Calcite | Level 5

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 

Astounding
PROC Star

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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;
Kurt_Bremser
Super User

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

raafat
Calcite | Level 5

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. 

Kurt_Bremser
Super User

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

raafat
Calcite | Level 5

sorry i forgot the last variable 🙂

Ksharp
Super User

Reverse the order of vaiables.

 

index_of_1nd=whichn(largest(1,of col(*)),of col5-col1);
raafat
Calcite | Level 5

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

Ksharp
Super User
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;
raafat
Calcite | Level 5

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? 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 17 replies
  • 2247 views
  • 2 likes
  • 5 in conversation