How To Use The SAS Proc SQL Order By Statement?

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;
sas proc sql order by
Note: The ORDER BY clause changes the order of the output but not the order of the rows stored in the table.

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;
sas proc sql order by

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;
sas proc sql order by

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;
SAS proc sql order by

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;
How To Use The SAS Proc SQL Order By Statement?

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;
How To Use The SAS Proc SQL Order By Statement?

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;
How To Use The SAS Proc SQL Order By Statement?

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.

Every week we'll send you SAS tips and in-depth tutorials

JOIN OUR COMMUNITY OF SAS Programmers!

Subhro Kar is an Analyst with over five years of experience. As a programmer specializing in SAS (Statistical Analysis System), Subhro also offers tutorials and guides on how to approach the coding language. His website, 9to5sas, offers students and new programmers useful easy-to-grasp resources to help them understand the fundamentals of SAS. Through this website, he shares his passion for programming while giving back to up-and-coming programmers in the field. Subhro’s mission is to offer quality tips, tricks, and lessons that give SAS beginners the skills they need to succeed.