ORDER BY Clause
Specifies the order in which rows are displayed
in a result table.
ORDER BY order-by-item <ASC|DESC><,
… order-by-item <ASC|DESC>>;
is one of the following:
equates to a column's
the name of a column or a column alias. See column-name.
orders the data in
ascending order. This is the default order. If neither ASC nor DESC
is specified, the data is ordered in ascending order.
orders the data in
ORDER BY clause sorts the results of a query expression according
to the order specified in that query. When this clause is used, the
default ordering sequence is ascending, from the lowest value to the
highest. You can use the SORTSEQ= option to change the collating sequence
for your output. See PROC SQL Statement.
The order of the output rows that
are returned is guaranteed only for columns that are specified in
the ORDER BY clause.
The ORDER BY clause does
not guarantee that the order of the rows generated is deterministic.
The ANSI standard for SQL allows the SQL implementation to specify
whether the ORDER BY clause is stable or unstable. If the joint combination
of values that is referenced in an ORDER BY clause for a query are
unique in all of the rows that are being ordered, then the order of
rows that is generated by ORDER BY is always deterministic. However,
if the ORDER BY clause does not reference a joint combination of unique
values, then the order of rows is not deterministic if ORDER BY is
If an ORDER BY clause is omitted,
then a particular order to the output rows, such as the order in which
the rows are encountered in the queried table, cannot be guaranteed—even
if an index is present. 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.
If more than one order-by-item
specified (separated by commas), then the first one determines the
major sort order.
Integers can be substituted for
column names (that is, SELECT object-items) in the ORDER BY clause.
For example, if the order-by-item
2 (an integer), then the results are ordered by the values of the
second column. If a query expression includes a set operator (for
example, UNION), then use integers to specify the order. Doing so
avoids ambiguous references to columns in the table expressions. Note
that if you use a floating-point value (for example, 2.3) instead
of an integer, then PROC SQL ignores the decimal portion.
In the ORDER BY clause, you can
specify any column of a table or view that is specified in the FROM
clause of a query expression, regardless of whether that column has
been included in the query's SELECT clause. For example, this query
produces a report ordered by the descending values of the population
change for each country from 1990 to 1995:
order by pop95-pop90 desc;
NOTE: The query as specified involves
ordering by an item that
doesn't appear in its SELECT clause.
You can order the output by the
values that are returned by an expression. For example, if X is a
numeric variable, then the output of the following is ordered by the
integer portion of values of X:
select x, y
order by int(x);
Similarly, if Y is a character variable,
then the output of the following is ordered by the second character
of values of Y:
select x, y
order by substring(y from 2 for 1);
Note that an expression
that contains only numeric literals (and functions of numeric literals)
or only character literals (and functions of character literals) is