sashelp.class 原表:
Obs Name Sex Age Height Weight
1 Joyce F 11 51.3 50.5
2 Louise F 12 56.3 77.0
3 Alice F 13 56.5 84.0
4 Jane F 12 59.8 84.5
5 Janet F 15 62.5 112.5
6 Carol F 14 62.8 102.5
7 Judy F 14 64.3 90.0
8 Barbara F 13 65.3 98.0
9 Mary F 15 66.5 112.0
10 James M 12 57.3 83.0
11 Thomas M 11 57.5 85.0
12 John M 12 59.0 99.5
13 Jeffrey M 13 62.5 84.0
14 Henry M 14 63.5 102.5
15 Robert M 12 64.8 128.0
16 William M 15 66.8 112.0
17 Ronald M 15 67.0 133.0
18 Alfred M 14 69.0 112.5
19 Philip M 16 72.0 150.0
- top 5
方法1
data test1;
set sashelp.class;
proc sort;
by sex descending height;
run;
data test2;
set test1;
by sex;
if first.sex then count=0;
count+1;
output;
run;
data test3;
set test2;
where count<=5;
drop weight;
run;
Obs Name Sex Age Height count
1 Mary F 15 66.5 1
2 Barbara F 13 65.3 2
3 Judy F 14 64.3 3
4 Carol F 14 62.8 4
5 Janet F 15 62.5 5
6 Philip M 16 72.0 1
7 Alfred M 14 69.0 2
8 Ronald M 15 67.0 3
9 William M 15 66.8 4
10 Robert M 12 64.8 5
方法2
proc sql;
select a.sex, a.name, a.height, (select count(distinct b.height) from sashelp.class b where b.height >= a.height and a.sex = b.sex ) as h_rank
from sashelp.class a
where calculated h_rank <= 5
order by sex, h_rank;
quit;
Sex Name Height h_rank
F Mary 66.5 1
F Barbara 65.3 2
F Judy 64.3 3
F Carol 62.8 4
F Janet 62.5 5
M Philip 72 1
M Alfred 69 2
M Ronald 67 3
M William 66.8 4
M Robert 64.8 5
- 最大值
方法1
data test1;
set sashelp.class;
proc sort;
by sex descending height;
run;
data test2;
set test1;
if first.sex then output;
by sex;
run;
Obs Name Sex Age Height Weight
1 Mary F 15 66.5 112
2 Philip M 16 72.0 150
方法2
proc sql;
select *, max(height) as H_max
from sashelp.classgroup by sex
having height=H_max;
quit;
Name Sex Age Height Weight H_max
Mary F 15 66.5 112 66.5
Philip M 16 72 150 72
方法3,
proc sql;
select a.name, a.sex, a.height from sashelp.class a
where height = (select max(height) from sashelp.class where sex = a.sex)
order by a.sex;
quit;
Name Sex Height
Mary F 66.5
Philip M 72
- 第二大值
方法1
data test1;
set sashelp.class;
proc sort;
by sex descending height;
run;
data test2;
set test1;
by sex;
if first.sex then count=0;
count+1;
output;
run;
data test3;
set test2;
where count = 2;
drop weight;
run;
Obs Name Sex Age Height count
1 Barbara F 13 65.3 2
2 Alfred M 14 69.0 2
方法2
proc sql;
select a.sex, a.name, a.height,
(select count(distinct b.height) from sashelp.class b where b.height >= a.height and a.sex = b.sex ) as h_rank
from sashelp.class a
where calculated h_rank = 2
order by sex, h_rank;
quit;
Sex Name Height h_rank
F Barbara 65.3 2
M Alfred 69 2
- 第N大
方法1
data test1;
set sashelp.class;
proc sort;
by sex descending height;
run;
data test2;
set test1;
by sex;
if first.sex then count=0;
count+1;
output;
run;
%let n = 6;
data test3;
set test2;
where count = &n;
drop weight;
run;
Obs Name Sex Age Height count
1 Jane F 12 59.8 6
2 Henry M 14 63.5 6
方法2
%let n = 6;
proc sql;
select a.sex, a.name, a.height, (select count(distinct b.height)
from sashelp.class b where b.height >= a.height and a.sex = b.sex ) as h_rank
from sashelp.class awhere calculated h_rank = &n
order by sex, h_rank;
quit;
Sex Name Height h_rank
F Jane 59.8 6
M Henry 63.5 6
====