How to find six th max value in a table
Do you want to do this for the entire table as a whole or for individual by-groups?
Do you want to do this for the entire table as a whole or for individual by-groups?
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
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
@yabwon agree 🙂
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
data max;
input id;
cards;
66
44
23
1
2
3
44
99
4
33
45
88
96
114
553
;
run;
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;
@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;
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: