*Copyright @ www.mycsg.in;

What is the word meaning of 'sort'?

arrange systematically in groups; separate according to type.
the arrangement of data in a prescribed sequence.

Common Scenarios where ordering (sorting of observations in a dataset) is required:

Display the student with top score as the first row in the dataset
Display the student with lowest height as the first row in the dataset/Order the student records based on height
Display the student with highest weight as the first row in the dataset/Order the student records based on descending weight
Order the student records based on their first name
Order the student records based on their first name, if there exists more than one student with same first name use their last names to break the ties
Order the student records such that all female records appear before male student records
Order the demographics dataset such that the subject with earliest informed consent signed date comes first
Order the exposure dataset such that the earliest exposure record appears first within a subject
Order the lab test results based on time of sample collection and test category
Order the adverse events such that the most commonly occurred event appears on top

What does 'proc sort' do?

The sort procedure orders SAS data set observations by the values of one or more character or numeric variables

Create some example datasets

Create a dataset named 'class01' in work library as a copy of sashelp.class dataset
Create a dataset named 'classes01' by appending sashelp.class twice, and create a variable named class and assign a value of 'Class 1' for first instance of records and 'Class 2' for the second instance of records

data class01;
   set sashelp.class;
run;
 
data classes01;
   set sashelp.class(in=a) sashelp.class(in=b);
   length class $10;
   if a then class="Class 1";
   if b then class="Class 2";
run;

Sorting a dataset based on the values of a single variable in ascending sequence and creating a new dataset

Question	Answer
How do we specify the name of input dataset to be used by proc sort?	We need to specify the name of the input dataset using data= option on the proc sort statement
What happens if we do not provide data= option?	The last created dataset in the current SAS session is used as input for proc sort
How do we specify the name of the output dataset in which the sorted observations are to be stored?	We need to specify the name of the output dataset using out=option on the proc sort statement
What happens if we do not provide out= option?	Input dataset gets overwritten with the sorted dataset
How do we instruct SAS to sort the observations based on particular variables?	We need to specify the names of the sorting variables in the by statement
What happens if we do not provide by variables or a by statement?	Returns an Error as by statement is a mandatory statment in proc sort
What is the default sort order for a variable?	By default SAS sorts observations based on the ascending values of the variables listed

Create a dataset named class_sort_asc_age by sorting the observations of class01 dataset based on ascending values in age variable

Specify class01 as input dataset using data= option
Specify class_sort_asc_age as output dataset using out=option
Specify the age variable on the by statement

proc sort data=class01 out=class_sort_asc_age;
   by age;
run;

Create a dataset named class_sort_asc_height by sorting the observations of class01 dataset based on ascending values in height variable

Specify class01 as input dataset using data= option
Specify class_sort_asc_height as output dataset using out=option
Specify the height variable on the by statement

Question	Answer
Which observation appears first when there is more than one record with the same value in the specified by variable?	When there is more than one observation in the input dataset with the same value in the specified by variables, the observation which appears first in the input dataset with that value will appear first in the output dataset

Notice Janet and Jeffrey's records in the output dataset to see the above scenario
Notice Mary and William's records in the output dataset to see the above scenario

proc sort data=class01 out=class_sort_asc_height;
   by height;
run;

Sorting a dataset based on the values of a single variable in descending sequence and creating a new dataset

Question	Answer
How do you instruct SAS to sort the observations based on the descending values of a variable?	We need to specify the keyword 'descending' before the name of the variable for which we want to sort based on descending values

Create a dataset named class_sort_dsc_age by sorting the observations of class01 dataset based on descending values in age variable

Specify class01 as input dataset using data= option
Specify class_sort_dsc_age as output dataset using out=option
Specify the age variable on the by statement
Specify the keyword descending before the name of the variable (age)

proc sort data=class01 out=class_sort_dsc_age;
   by descending age;
run;

Create a dataset named class_sort_dsc_height by sorting the observations of class01 dataset based on descending values in height variable

Specify class01 as input dataset using data= option
Specify class_sort_dsc_height as output dataset using out=option
Specify the height variable on the by statement
Specify the keyword descending before the name of the variable (height)

proc sort data=class01 out=class_sort_dsc_height;
   by descending height;
run;

Sorting a dataset based on multiple variables and creating a new dataset

We need to specify the list of sorting variables separated by space on the by statement

Create a dataset named class_sort_age_sex by sorting the observations of class01 dataset based on ascending values in age and sex variables

Records with same age get grouped together first such that lowest age value records come on top
If there exists more than one record in a age group, the records with lowest value in sex appear on top
If there exists more than one record in a age and sex group, the records get sorted based on their order in the input dataset

proc sort data=class01 out=class_sort_age_sex;
   by age sex;
run;

Create a dataset named classes_sort_class_age_sex by sorting the observations of classes01 dataset based on ascending values in class, age and sex variables

Records with same value in class variable get grouped together first such that lowest class value records come on top
If there exists more than one record in a class group, the records with lowest value in age appear on top
If there exists more than one record in a class and age group, then the records with lowest value in sex variable appear on top
If there exists more than one record in a class,age and sex group, the records get sorted based on their order in the input dataset

proc sort data=classes01 out=class_sort_class_age_sex;
   by class age sex;
run;

Create a dataset named classes_sort_name_class by sorting the observations of classes01 dataset based on ascending values in name and class variables

Records with same value in name variable get grouped together first such that lowest name value records come to top
If there exists more than one record with same name value, then the record with lowest value in class variable comes to top
If there exists more than one record in name and class combination, the records get sorted based on their appearance in the input dataset

proc sort data=classes01 out=classes_sort_name_class;
   by name class;
run;

Create a dataset named class_sort_aage_dheight by sorting class01 dataset based on ascending age and descending height

Records with same age value gets grouped together first such that the records with lowest age appear on top
If there exists more than one record with same age value, the record with highest age value comes to top
If there exists more than one record with same values in age and height variable, those records appear in the output dataset in the same order as in input dataset
Notice in the code below that the keyword 'descending' is used only before height variable, and no keyword is used before age variable

proc sort data=class01 out=class_sort_aage_dheight;
   by age descending height;
run;

Create a dataset named class_sort_dage_dheight by sorting classes01 dataset based on descending age and descending height

Records with same age value gets grouped together first such that the records with highest age appear on top
If there exists more than one record with same age value, the record with highest age value comes to top
If there exists more than one record with same values in age and height variable, those records appear in the output dataset in the same order as in input dataset
Notice in the code below that the keyword 'descending' is used before height variable and also used before age variable

proc sort data=classes01 out=class_sort_dage_dheight;
   by descending age descending height;
run;

Create a dataset named class_sort_dage_aheight by sorting classes01 dataset based on descending age and ascending height

Records with same age value gets grouped together first such that the records with highest age appear on top
If there exists more than one record with same age value, the record with lowest age value comes to top
If there exists more than one record with same values in age and height variable, those records appear in the output dataset in the same order as in input dataset
Notice in the code below that the keyword 'descending' is used before age variable and no keyword is used before height variable

proc sort data=classes01 out=class_sort_dage_aheight;
   by descending age height;
run;