SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

title 'Areas of U.S. States in Square Miles';

select Name label='State', Area format=comma10.

from sql.unitedstates;

Note: Using the LABEL= keyword is optional. For example, the following two select

clauses are the same:

select Name label='State', Area format=comma10.

select Name 'State', Area format=comma10.

Output 2.14 Specifying Column Attributes

Sorting Data

Overview of Sorting Data

You can sort query results with an ORDER BY clause by specifying any of the columns

in the table, including columns that are not selected or columns that are calculated.

Unless an ORDER BY clause is included in the SELECT statement, 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. Therefore,

if you want your result table to appear in a particular order, then use the ORDER BY

clause.

Sorting Data 37

For more information and examples, see the “ORDER BY Clause” on page 265.

Sorting by Column

The following example selects countries and their populations from the Sql.Countries

table and orders the results by population:

libname sql 'SAS-library';

proc sql outobs=12;

title 'Country Populations';

select Name, Population format=comma10.

from sql.countries

order by Population;

Note: When you use an ORDER BY clause, you change the order of the output but not

the order of the rows that are stored in the table.

Note: The PROC SQL default sort order is ascending.

Output 2.15 Sorting by Column

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 Sql.Countries table

by two columns, Continent and Name:

libname sql 'SAS-library';

38 Chapter 2 • Retrieving Data from a Single Table

proc sql outobs=12;

title 'Countries, Sorted by Continent and Name';

select Name, Continent

from sql.countries

order by Continent, Name;

Output 2.16 Sorting by Multiple Columns

Note: The results list countries without continents first because PROC SQL sorts

missing values first in an ascending sort.

Specifying a Sort Order

To order the results, specify ASC for ascending or DESC for descending. You can

specify a sort order for each column in the ORDER BY clause.

When you specify multiple columns in the ORDER BY clause, the first column

determines the primary row order of the results. Subsequent columns determine the order

of rows that have the same value for the primary sort. The following example sorts the

Sql.Features table by feature type and name:

libname sql 'SAS-library';

proc sql outobs=12;

title 'World Topographical Features';

select Name, Type

from sql.features

order by Type desc, Name;

Sorting Data 39

Note: The ASC keyword is optional because the PROC SQL default sort order is

ascending.

Output 2.17 Specifying a Sort Order

Sorting by Calculated Column

You can sort by a calculated column by specifying its alias in the ORDER BY clause.

The following example calculates population densities and then performs a sort on the

calculated Density column:

libname sql 'SAS-library';

proc sql outobs=12;

title 'World Population Densities per Square Mile';

select Name, Population format=comma12., Area format=comma8.,

Population/Area as Density format=comma10.

from sql.countries

order by Density desc;

40 Chapter 2 • Retrieving Data from a Single Table

Output 2.18 Sorting by Calculated Column

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

that has no alias. The following example does not assign an alias to the calculated

Density column. Instead, the column position of 4 in the ORDER BY clause refers to the

position of the calculated column in the SELECT clause:

libname sql 'SAS-library';

proc sql outobs=12;

title 'World Population Densities per Square Mile';

select Name, Population format=comma12., Area format=comma8.,

Population/Area format=comma10. label='Density'

from sql.countries

order by 4 desc;

Note: PROC SQL uses a label, if one has been assigned, as a heading for a column that

does not have an alias.

Sorting Data 41