topic Re: 6th max value in SAS Programming

6th max value

BrahmanandaRao — Wed, 02 Dec 2020 08:10:55 GMT

How to find six th max value in a table

Re: 6th max value

PeterClemmensen — Wed, 02 Dec 2020 08:11:46 GMT

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

Re: 6th max value

BrahmanandaRao — Wed, 02 Dec 2020 08:14:02 GMT

entire table and by groups also

Re: 6th max value

PeterClemmensen — Wed, 02 Dec 2020 08:28:16 GMT

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

Re: 6th max value

yabwon — Wed, 02 Dec 2020 08:35:42 GMT

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

Re: 6th max value

PeterClemmensen — Wed, 02 Dec 2020 08:35:46 GMT

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.

Re: 6th max value

PeterClemmensen — Wed, 02 Dec 2020 08:36:47 GMT

@yabwon agree 🙂

Re: 6th max value

yabwon — Wed, 02 Dec 2020 08:54:40 GMT

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

Re: 6th max value

BrahmanandaRao — Wed, 02 Dec 2020 09:51:04 GMT

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

Re: 6th max value

yabwon — Wed, 02 Dec 2020 10:33:36 GMT

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;

Re: 6th max value

Kurt_Bremser — Wed, 02 Dec 2020 10:46:23 GMT

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