The order
of rows in the output of a PROC SQL query cannot be guaranteed, unless
you specify a sort order. To sort rows by the values of specific columns,
you can use the ORDER BY clause in the SELECT statement. Specify the
keywords ORDER BY, followed by one or more column names separated
by commas.
In the following PROC
SQL query, the ORDER BY clause sorts rows by values of the column
JobCode:
proc sql;
select empid,jobcode,salary,
salary*.06 as bonus
from sasuser.payrollmaster
where salary<32000
order by jobcode;
Note: In this example, the ORDER
BY clause is the last clause in the SELECT statement, so the ORDER
BY clause ends with a semicolon.
In the output of the
sample query, shown below, the rows are sorted by the values of JobCode.
By default, the ORDER BY clause sorts rows in ascending order.
To sort rows in descending
order, specify the keyword DESC following the column name. For example,
the preceding ORDER BY clause could be modified as follows:
order by jobcode desc;
In the ORDER BY clause,
you can alternatively reference a column by the column's position
in the SELECT clause list rather than by name. Use an integer to indicate
the column's position. The ORDER BY clause in the preceding PROC
SQL query has been modified, below, to specify the column JobCode
by the column's position in the SELECT clause list (
2)
rather than by name:
proc sql;
select empid,jobcode,salary,
salary*.06 as bonus
from sasuser.payrollmaster
where salary<32000
order by 2;