Ranking of variables is often necessary to analyze the performance or gain an insight on which are the values are on top or at the bottom and by using PROC RANK procedure, there is no need to write complex code using PROC SORT, MACRO calls and DATA STEPS to rank or decile these scores or values.
What is Proc Rank?
PROC RANK is a SAS procedure that calculates RANKS from one or more numerical variables in observations in the SAS dataset and creates a new data set capturing these rankings.
The rank procedure does not produce any printed output but has several options for specifying the order of rank, which handles relationships in variable values and can generate variable bins or groups based on the specification of the GROUPS option.
Computing rank of numeric variables
In the following example from SASHELP.CLASS dataset, I had used PROC RANK to rank students’ based on their weight from lowest to highest.
proc rank data=sashelp.class out=class_r_low; var weight; ranks r_weight; run; proc sort data=class_r_low; by weight; proc print;
OUT option tells SAS to store the output of the RANK procedure in a SAS dataset.
VAR option is the variable for which you want to compute the rank.
RANKS option creates a new variable named r_weight with the rank value. If the ranks option is not given, the rank values will overwrite the original variable.
The weight variable is ranked in ascending order by default. Observation 4 and 5 have the same weight so the same rank is shared for them.
By default, if there are ties proc rank will calculate the mean and will share the rank.
But, how is 4.5 calculated behind the scene?
For, observations 4 and 5 the sum of the raw ranks of the tied value ( 4 + 5 ) is calculated and this is divided by the number of observations which is 2 in this case.
So the average rank is calculated as (4+5)/2 = 4.5
Ranking in descending order
For, ranking in descending order, i.e The largest value of a variable has rank 1 and the lowest has the last rank, you can use the
DESCENDING keyword as illustrated in the below example.
proc rank data=sashelp.class out=class_r_weight descending; var weight; ranks r_weight; run; proc sort data=class_r_low(keep=name weight r_weight); by weight; proc print;
You can generate ranks in groups like quartiles(4th), quintiles(5th), deciles(10th) or percentiles(100). The variable named in the RANKS statement will contain values ranging from 0 to 4 for the groups.
proc rank data=sashelp.class out=class_r_low group=4; var age; ranks r_age; run; proc sort data=class_r_low; by age; run;
The values are assigned to groups ranging from 0 to the number of groups -1 based on tied values.
Partitioning Observations into Groups Based on Ranks
In some cases, you might want to find out the highest or lowest two Ranks for analysis. In this case, you can use the GROUP option to group ranks.
I have eliminated the duplicate age by using
PROC SORT with the
NODUPKEY option from the input dataset for an easy understanding. I will explain how to handle ties later in this article.
proc sort data=sashelp.class out=class3 nodupkey; by age; run;
Now,you see what happens when I applied the group= option for the input dataset.
proc rank data=class3 out=class_r_low group=2; var age; ranks r_age; run;
Proc Rank places the data into the number of groups you specify. In this example, groups are 2. Two Students with the smallest age are assigned to group 0 and two students with the largest age value are placed in group 1.
If the number of observations in the input dataset is the multiple of the number of groups you specify then each group will have the same number of observations.
If you had one more observation in the input dataset, that observation would wind up in Group 1 which is the higher group.
In this way, you can use Proc Rank to get an insight into the top 5% and bottom 5 % of the data. For this, you will need to specify the group as 20.
Ranking with BY group variable
You can rank variables based on the group. For example, ranking the age of students grouped by gender. It is important to sort the data based on the BY variables by which you want to group.
/*Sorting the data based on sex*/ proc sort data=sashelp.class out=class; by sex; run; proc rank data=class out=class_r_low ties=low; by sex; var age; ranks r_age; run; proc print data=class_r_low n; by sex; run;
Dealing with TIES
This is the most important and useful feature in this procedure. The TIES option allows us to control the ranking number when there is a TIE between the same Rank. There are four options that you can use for dealing with TIES.
LOWWith the LOW option specified, the tied Values are assigned to the lower rank.
HIGHTied values are assigned to the higher rank.
MEANTied values are assigned the mean of the corresponding ranks which is also the default option.
DENSEThe ranks are consecutive integers that begin with 1 end with a number of unique values of the VAR variable.
Below is an example of all the options and its result on the Ranking of the variable age.
Using Proc Rank to find TOP N rows per BY Group
You can solve the “top N” questions using the Proc Rank step, and the following example demonstrates how to select the top 3 students with the highest weight value for sex.
proc sort data=sashelp.class out=class; by sex; run; proc rank data=class out=r_test descending; by sex; var weight; ranks r_weight; run; proc sort data=r_test; by sex r_weight; where r_weight <=2; run;