6th max value

[BrahmanandaRao]

How to find six th max value in a table 

[PeterClemmensen · Accepted solution]

Do you want to do this for the entire table as a whole or for individual by-groups?

[BrahmanandaRao]

entire table and by groups also

[PeterClemmensen]

Without much knowledge of your data, I'm going to assume that your data is sorted by the group variable, but not the 'value' variable of interest.

See if you can use this as a template

proc sort data = sashelp.class out = class;
   by sex;
run;

data want;
   if _N_ = 1 then do;
      declare hash h (dataset : 'class(obs=0)', ordered : 'd', multidata : 'Y'); 
      h.definekey ('Height');
      h.definedata (all : 'y');
      h.definedone();
      declare hiter hi ('h');
   end;
 
   do until (last.Sex);                                                      
      set class;
      by Sex;
      h.add();
   end;
 
   do _N_ = 1 by 1 until (hi.next() ne 0 | _N_ = 6); 
   end;

   output;
 
   _N_ = hi.first();                                                             
   _N_ = hi.prev();
   h.clear();    
run;

Result:

Name  Sex Age Height Weight 
Jane  F   12  59.8   84.5 
Henry M   14  63.5   102.5 

[yabwon]

I think if we decide to sort we can do it by both variables.

proc sort data = sashelp.class out = class;
   by sex Height;
run;

data class6th;
  do _N_ = 1 by 1 until(last.sex);
    set class;
    by Sex;
    if _N_ = 6 then output; 
  end;
run;

proc print data = class6th;
run;

And for "no sorting at all" case hash-of-hashes could be useful.

Bart

[PeterClemmensen]

@yabwon agree 🙂

[yabwon]

To find global N-th on table without sorting and groups it could be like that:

%let size = 6;
data nosort6th;
  array a[&size.] _temporary_;

  do _N_ = 1 to &size.;
    set class nobs = nobs;
    a[_N_] = Height;
  end;

  call sortn(of a[*]);

  if nobs > &size. then
    do until(eof);
      set class(firstobs=%eval(&size.+1)) end=eof;
      if height>a[1] then
        do;
          a[1] = height;
          call sortn(of a[*]);
        end;
    end;

  value&size. = a[1];

  put value&size. =;
  keep value&size.;
  output;
  stop;
run;

But `&size.` must be <= `nobs`.

Bart

[BrahmanandaRao]

data max;
input id;
cards;
66
44
23
1
2
3
44
99
4
33
45
88
96
114
553
;
run;

[yabwon]

data max;
input id;
cards;
66
44
23
1
2
3
44
99
4
33
45
88
96
114
553
;
run;

%let data = max;
%let vaiable = id;
%let size = 6;

data nosort6th;
  array a[&size.] _temporary_;

  do _N_ = 1 to &size.;
    set &data. nobs = nobs;
    a[_N_] = &vaiable.;
  end;

  call sortn(of a[*]);

  if nobs > &size. then
    do until(eof);
      set &data.(firstobs=%eval(&size.+1)) end=eof;
      if &vaiable. > a[1] then
        do;
          a[1] = &vaiable.;
          call sortn(of a[*]);
        end;
    end;

  value&size. = a[1];

  put value&size. =;
  keep value&size.;
  output;
  stop;
run;

[Kurt_Bremser]

---

@BrahmanandaRao wrote:

data max;
input id;
cards;
66
44
23
1
2
3
44
99
4
33
45
88
96
114
553
;
run;

---

Run this:

proc rank
  data=max
  out=want (where=(rank=6))
  descending
  ties=low
;
var id;
ranks rank;
run;

[PeterClemmensen]

The example above handles the by-group case in a single pass. 

For the entire table, find an example online. There are tons. For example, Proc Rank is a fine tool for this.