The ORDER BY Clause
A Brief Overview
The order of rows in
the output of a PROC SQL query cannot be guaranteed, unless you specify
a sort order with the ORDER BY clause. Specify the keywords ORDER
BY, followed by one or more column names separated by commas.
Figure 1.1 SORT Methods for ORDER BY
Without an ORDER BY
clause, the order of the output rows is determined by the internal
processing of PROC SQL, the default collating sequence of SAS, and
your operating environment. Therefore, if you want your result table
to appear in a particular order, use the ORDER BY clause. Here are
details about sort order:
-
The PROC SQL default sort order is ascending.
-
PROC SQL sorts missing values before nonmissing values. Therefore, when you specify ascending order, missing values appear first in the query results.
-
When you use an ORDER BY clause, you change the order of the results but not the order of the rows that are stored in the source table.
-
If multiple ORDER BY columns are specified, the first one determines the major sort order.
ORDER BY Clause Syntax
|
Syntax, ORDER BY clause:
PROC SQL <options>;
SELECT column-1 <,...column-n>
FROM input-tables
WHERE expression
GROUP
BY column-name <,column-name>
ORDER
BY column-name <DESC> <,column-name>;
QUIT;
|
Example: Ordering Rows by the Values of a Single Column
In the following PROC
SQL query, the ORDER BY clause sorts rows by values of the column
JobCode. The ORDER BY clause is the last clause in the SELECT statement,
so the ORDER BY clause ends with a semicolon.
proc sql;
select empid,jobcode,salary,
salary*.06 as bonus
from certadv.payrollmaster
where salary<32000
order by jobcode;
quit;
In the sample query
output shown below, the rows are sorted by the values of JobCode.
By default, the ORDER BY clause sorts rows in ascending order.
Output 1.21 PROC SQL Query Result Sorted by JobCode
Example: Ordering by Multiple Columns
To sort rows by the values
of two or more columns, list multiple column names or numbers in the
ORDER BY clause. Use commas to separate the column names or numbers.
proc sql;
select empid,jobcode,salary,
salary*.06 as bonus
from certadv.payrollmaster
where salary<32000
order by jobcode,empid;
quit;
The rows are sorted
by JobCode and then by EmpID, as shown in the following output.
Output 1.22 PROC SQL Query Result Sorted by JobCode and EmpID
Example: Ordering Columns by Position
You can order columns
by their position in the SELECT clause. In the following PROC SQL
query, the ORDER BY clause sorts the values of the fourth column and
the second column.
proc sql;
select empid, jobcode, salary, dateofhire
from certadv.payrollmaster
where salary<32000
order by 4, 2;
quit;
The following SQL query
result is sorted by the fourth position, which is DateofHire and then
by the second position, which is JobCode.
Output 1.23 PROC SQL Query Result: Ordering by Position
Last updated: October 16, 2019
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.