Sorting is an important process when working with data. It allows you to organize the data to make it easier to work with and understand. There are several ways to sort data, but one of the most popular methods is SAS Proc SQL Order By.
Proc SQL is a powerful tool that can be used to sort data in various ways. This article will show you how to sort data using Proc SQL. We will also provide some examples to help you better understand proc SQL sorts data.
Sorting by Column
The following example selects the name and their age from the sashelp.class table and orders the results by age:
proc sql outobs=5;
select Name, age from sashelp.class order by age;
quit;
Sorting by Multiple Columns
You can sort by more than one column by specifying the column names, separated by commas, in the ORDER BY clause. The following example sorts the sashelp.class table by two columns, sex and name:
proc sql outobs=12;
title 'CLASS, Sorted by sex and Name';
select Name, sex
from sashelp.class
order by sex, Name;
Sorting by Calculated Column
You can sort by a calculated column by specifying its alias in the ORDER BY clause. The following example calculates BMI and then performs a sort on the calculated BMI column:
proc sql outobs=10;
title 'Class dataset sorted by BMI';
select Name, sex, weight label="Weight(kgs)", height label="Height(cms)",
round(divide((weight*703), height**2),0.01) as BMI from sashelp.class order
by bmi desc;
Sorting by Column Position
You can sort by any column within the SELECT clause by specifying its numerical position. By specifying a position instead of a name, you can sort by a calculated column with no alias.
The following example does not assign an alias to the calculated BMI column. Instead of referring to the column name in the ORDER BY clause, it refers to the position of the calculated column in the SELECT clause:
proc sql outobs=10;
title 'Class dataset sorted by BMI';
select Name, sex, weight label="Weight(kgs)", height label="Height(cms)",
round(divide((weight*703), height**2),0.01) label='BMI' from sashelp.class order by 5
desc;
Sorting by Columns That Are Not Selected
Query results can be sorted by columns not included in the query. For example, the following query returns all rows of the sashelp.class table sorted by age, despite not including the age column:
proc sql outobs=12;
title 'CLASS, Sorted by age and Name';
select Name, sex from sashelp.class order by age, Name;
Specifying a Different Sorting Sequence
With SORTSEQ=, PROC SQL specifies the order in which a query is sorted when the ORDER BY clause is present. If you want to sort differently from your operating system’s default, use this option.
This example demonstrates the functionality of SORTSEQ with PROC SQL:
options sortseq=reverse;
proc sql outobs=10;
select * from sashelp.class order by name;
quit;
Sorting Columns That Contain Missing Values
PROC SQL sorts nulls, or missing values, before character or numeric data. Therefore, missing values appear first in the query results when you specify ascending order.
proc surveyselect data=sashelp.heart out=sample method=srs sampsize=10;
run;
proc sql;
select status, deathcause from sample order by deathcause;
Conclusion
This article showed how to sort data using Proc SQL. To do this, you need to use the ORDER BY clause in the SELECT statement. You can also use the DESC option to sort in descending order.
