Sorting Data by Multiple Columns
Chong Ho Yu, Ph.D.

 

Consider this scenario. There is a dataset called "one," in which the data are collected from multiple attempts of a test by the same examinees. Each examinee has unlimited chances to take the test until he/she passes the exam. As a result, some users may have six attempts, some may have five, some may have four, and so on. You want to sort the data in the way that examinees with more number of attempts are shown first. You may say, "A piece of cake. Use PROC SORT; BY descending attempt user." Try it and I guarantee that you will not get what you want. This kind of sorting will return something like data "one_b."

You can keep trying sorting different variables in different order but it will not work. To sort the data properly, you need to take one more step: PROC SUMMARY. The following code illustrates the procedure:

Data One

User Attempt Score
Alex 4 100
Alex 1 99
Alex 2 97
Alex 3 99
Jody 5 99
Jody 6 100
Jody 2 90
Jody 1 87
Jody 4 97
Jody 3 89
Tom 3 100
Tom 1 91
Tom 2 87
 

Data One_B

User Attempt Score
Jody 6 100
Jody 5 99
Alex 4 100
Jody 4 97
Alex 3 99
Jody 3 89
Tom 3 100
Alex 2 97
Jody 2 90
Tom 2 87
Alex 1 99
Jody 1 87
Tom 1 91

 

/* create a new variable called n. This procedure will count the number of attempt for

each examinee. */

proc summary data=one; class user; var attempt; output out=two n=n; run;

/* erase redundant observations and variables */

data two; set two;

if user = " " then delete;

drop _TYPE_ _FREQ_; run;

proc sort data=one; by user;

proc sort data=two; by user;

/* Merge the original dataset with the PROC SUMMARY's result. Sort the data by n first*/

data three; merge one two; by user; run;

/* Since n is only a temporary variable. Drop it in the final output */

proc sort data=three; by descending n user descending attempt; run;

data four; set three; drop n; run;

 

Data Two:
From PROC SUMMARY
 
User n
Alex 6
Jody 4
Tom 3

 

 Data Three:
From PROC SORT by n
 
User Attempt Score n
Jody 6 100 6
Jody 5 99 6
Jody 4 97 6
Jody 3 89

6
Jody 2 90 6
Jody 1 87 6
Alex 4 100 4
Alex 3 99 4
Alex 2 97 4
Alex 1 99 4
Tom 3 100 3
Tom 2 87 3
Tom 1 91 3

 

 Data Four:
Drop n
 
User Attempt Score
Jody 6 100
Jody 5 99
Jody 4 97
Jody 3 89
Jody 2 90
Jody 1 87
Alex 4 100
Alex 3 99
Alex 2 97
Alex 1 99
Tom 3 100
Tom 2 87
Tom 1 91

 

Navigation

SAS tips contents

Computer write-ups contents

Simplified Navigation

Table of Contents

Search Engine

Contact