O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required