学习笔记贴，数据集分组并在各组内排名

为简明起见，我以sashelp.class 为例，介绍如何对对数据集按性别分成男、女两组，并按身高（height）从矮到高排列。

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

方法1，first.variable

data test1;

set sashelp.class;

proc sort;

by sex height;

run;

data test2;

set test1;

by sex;

if first.sex then rank_h1 = 0;

rank_h1 + 1 ;

drop age weight;

run;

Obs Name Sex Height rank_h1

1 Joyce F 51.3 1

2 Louise F 56.3 2

3 Alice F 56.5 3

4 Jane F 59.8 4

5 Janet F 62.5 5

6 Carol F 62.8 6

7 Judy F 64.3 7

8 Barbara F 65.3 8

9 Mary F 66.5 9

10 James M 57.3 1

11 Thomas M 57.5 2

12 John M 59.0 3

13 Jeffrey M 62.5 4

14 Henry M 63.5 5

15 Robert M 64.8 6

16 William M 66.8 7

17 Ronald M 67.0 8

18 Alfred M 69.0 9

19 Philip M 72.0 10

方法2，proc rank

data test1;

set sashelp.class;

proc sort;

by sex height;

run;

proc rank data=test1 out=test3 (drop=age weight) ;

var height;

by sex;

ranks Mingci;

run;

Obs Name Sex Height Mingci

1 Joyce F 51.3 1

2 Louise F 56.3 2

3 Alice F 56.5 3

4 Jane F 59.8 4

5 Janet F 62.5 5

6 Carol F 62.8 6

7 Judy F 64.3 7

8 Barbara F 65.3 8

9 Mary F 66.5 9

10 James M 57.3 1

11 Thomas M 57.5 2

12 John M 59.0 3

13 Jeffrey M 62.5 4

14 Henry M 63.5 5

15 Robert M 64.8 6

16 William M 66.8 7

17 Ronald M 67.0 8

18 Alfred M 69.0 9

19 Philip M 72.0 10

方法3，proc sql

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 rank_height

from sashelp.class a

order by sex, rank_height;

quit;

Sex Name Height rank_height

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

F Jane 59.8 6

F Alice 56.5 7

F Louise 56.3 8

F Joyce 51.3 9

M Philip 72 1

M Alfred 69 2

M Ronald 67 3

M William 66.8 4

M Robert 64.8 5

M Henry 63.5 6

M Jeffrey 62.5 7

M John 59 8

M Thomas 57.5 9

M James 57.3 10

如果不要求男女分组，来个全班总排名，稍有不同：

用自动变量_N_ ：

data test5;

set sashelp.class;

proc sort;

by height;

run;

data test6;

set test5;

by height;

tot_ranking=_N_ ;

drop age weight;

run;

tot_

Obs Name Sex Height ranking

1 Joyce F 51.3 1

2 Louise F 56.3 2

3 Alice F 56.5 3

4 James M 57.3 4

5 Thomas M 57.5 5

6 John M 59.0 6

7 Jane F 59.8 7

8 Janet F 62.5 8

9 Jeffrey M 62.5 9

10 Carol F 62.8 10

11 Henry M 63.5 11

12 Judy F 64.3 12

13 Robert M 64.8 13

14 Barbara F 65.3 14

15 Mary F 66.5 15

16 William M 66.8 16

17 Ronald M 67.0 17

18 Alfred M 69.0 18

19 Philip M 72.0 19

或者：

proc sql;

select a.name, a.sex, a.height, (select count(distinct b.height)

from sashelp.class b where b.height <= a.height) as H_ranking

from sashelp.class a

order by H_ranking;

quit;

（注意，有两个第8名）

Name Sex Height H_ranking

Joyce F 51.3 1

Louise F 56.3 2

Alice F 56.5 3

James M 57.3 4

Thomas M 57.5 5

John M 59 6

Jane F 59.8 7

Jeffrey M 62.5 8

Janet F 62.5 8

Carol F 62.8 9

Henry M 63.5 10

Judy F 64.3 11

Robert M 64.8 12

Barbara F 65.3 13

Mary F 66.5 14

William M 66.8 15

Ronald M 67 16

Alfred M 69 17

Philip M 72 18