Sorting on Multiple Variables

In the last topic, we learned PROC sort in SAS and saw that we could sort data values in ascending or descending order and also on the basis of our own chosen variable. We also studied that we can perform sorting by taking multiple variables simultaneously, but we have not done it practically.

Now, let's see how to sort data values by using multiple variables and what its limitations are.

SAS allows multiple variable sorting, so we can sort multiple variables at the same time. But just think, if we instruct SAS to sort multiple variables in ascending (or default) order, and all contain integer values so, which variable SAS should choose for sorting. Let's understand via an example:

data student;
input roll age $;
datalines;
101 14
102 13
103 15
104 12
;
run;
proc sort data=student;
    by roll age  ;
run;
proc print data=student;
    by roll age;
run;

In the above example, we are giving two variables roll and age for sorting, and both are integer values, so which variable SAS should choose for sorting, roll or age. The answer is, SAS will consider first declared variable, i.e. roll, and if the first variable has two or more than two same values then it will consider the second variable for sorting.

Example:

Suppose, there is a dataset named student and it includes data of students of a class such as a roll number, name, game, age, weight, and height. For sorting, we are using two variables, i.e. weight and age, and sorting order is default.

data student;
input roll name$ game$ age weight height$;
datalines;
1 Anuj football 22 64 161
5 Anuj tenis 23 64 155
2 Binita bedminton 30 64 159
7 Binita football 29 74 164
4 Anita tenis 29 65 153
3 Tarun football 21 70 162
6 Preeti bedminton 24 57 157
;
run;  
proc sort data=student;
    by weight age  ;
run;
proc print data=student;
    by weight age;
run;

Run code in SAS studio:

Output

In the output, we can see that the sorting is done on the basis of weight. But the weight of three students is the same so, in this situation, it considers age (for the sorting of only these three same values and after these values it will jump on the weight again) that is second declared variable.

See another example where the variable name that contains alphabetical values, is mentioned first.

data student;
input roll name$ game$ age weight height$;
datalines;
1 Anuj football 22 64 161
5 Lata tenis 23 60 155
2 Vinita bedminton 30 65 159
7 Kumar football 29 74 164
4 Anita tenis 29 65 153
3 Tarun football 21 70 162
6 Preeti bedminton 24 57 157
;
run;  
proc sort data=student;
    by name age  ;
run;
proc print data=student;
    by name age;
run;

Run code on SAS studio:

Output:

We can see in the output it is also sorted according to the variable name that is mentioned first.

Limitation

There is a limitation in the SAS sorting. In a case, when we refer a variable for sorting which contains alphabetical data values, but some values start with upper case letters and some are with lower case letters, then the sorting is done in the order like that, upper case letter sorted first and lower case letter in the last. Let's see the example:

data student;
input roll name$ game$ age weight height$;
datalines;
1 Anuj football 22 64 161
5 lata tennis 23 60 155
2 Vinita badminton 30 65 159
7 Kumar football 29 74 164
4 Anita tennis 29 65 153
3 Tarun football 21 70 162
6 preeti badminton 24 57 157
;
run;  
proc sort data=student;
    by name game ;
run;
proc print data=student;
    by ;
run;

Run code on SAS studio: