Second Maximum Values in given Dataset

[BrahmanandaRao]

Hi Guys,

Good Morning 

i have data set below

/*how to find out second max number in sas by using data step*/
data second;
input name$ number;
cards;
aravind 98
kiran 49
lahari 58
ahalya 97
amith 69
ankit 97
abhhi 70
;
run;

in the above data set i want second maximum numbers  here observe second max numbers are 97 two times

I  want output like below

Name Number

ahalya 97

ankit 97

[PeterClemmensen]

Why do you want to do this with a data step? Seems easier with eg PROC RANK like this?

proc rank
    data=second
    ties=dense descending
    out=want(where=(rank=2))
    ;
  var number;
  ranks rank;
run;

[BrahmanandaRao]

Hi Dray cut

Thanks  for your  supporting

But i want output in data step only not in proc step

Regards,

Anand

[PeterClemmensen]

Ok. Use a double DoW loop like this then.

data want(drop=_:);

   do until (lr1);
      set second end=lr1;
      if number gt _1 then _1=number;
      if (number lt _1) & (number ge _2) then _2=number;
   end;

   do until (lr2);
      set second end=lr2;
      if number=_2 then output;
   end;

run;

[BrahmanandaRao]

Thank you dray cut 

your are brilliant ☺

[BrahmanandaRao]

Thank you dray cut for your solution 

Regards,

Anand

[BrahmanandaRao · Accepted solution]

data second; input name$ number; cards; aravind 98 kiran 49 lahari 58 ahalya 97 amith 69 ankit 97 abhhi 70 ; run; output like below using only datastep not proc step Obs name number rank 1 ahalya 97 2 2 ankit 97

[PeterClemmensen]

Please see the code I provided above. Does it work for you?

[Ksharp]

data second;
input name$ number;
cards;
aravind 98
kiran 49
lahari 58
ahalya 97
amith 69
ankit 97
abhhi 70
;
run;
proc sort data=second out=temp;
by descending number;
run;
data want;
 set temp;
 by descending number;
 n+first.number;
 if n=2;
run;

[BrahmanandaRao]

Hi Ksharp 

Good Morning ,

Thank your for your support 

brilliant code smileyhappy:

[novinosrin]

data second;
input name$ number;
cards;
aravind 98
jack 98
kiran 49
lahari 58
ahalya 97
amith 69
ankit 97
abhhi 70
;

data want ;
if _n_=1 then do;
if 0 then set second;
   dcl hash H (dataset:'second',ordered: "d",multidata:'y') ;
   h.definekey  ("number") ;
   h.definedata ("number","name") ;
   h.definedone () ;
   dcl hiter hh('h');
end;
do _n_=1 by 1 while(hh.next()=0);
	if _n_=1 then rc2=number;
	if rc2 ne number then do;
		do while(h.do_over() eq 0);
		output;
		end;
		stop;
	end;
end;
drop rc:;
run;