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.