 |
|
| Retrieving Data from a Single Table |
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.
For more information and examples see the ORDER BY clause in Base SAS Procedures Guide.
| Sorting by Column |
The following example selects countries and their populations from the SQL.COUNTRIES table and orders the results by population:
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. ![[cautionend]](../../../../common/61991/HTML/default/images/cautend.gif)
Note: The PROC SQL default sort order
is ascending.
Country Populations
Name Population
-----------------------------------------------
Vatican City 1,010
Tuvalu 10,099
Nauru 10,099
Turks and Caicos Islands 12,119
Leeward Islands 12,119
Cayman Islands 23,228
San Marino 24,238
Liechtenstein 30,297
Gibraltar 30,297
Monaco 31,307
Saint Kitts and Nevis 41,406
Marshall Islands 54,535
| 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:
proc sql outobs=12; title 'Countries, Sorted by Continent and Name'; select Name, Continent from sql.countries order by Continent, Name;
Countries, Sorted by Continent and Name
Name Continent
------------------------------------------------------------------------
Bermuda
Iceland
Kalaallit Nunaat
Algeria Africa
Angola Africa
Benin Africa
Botswana Africa
Burkina Faso Africa
Burundi Africa
Cameroon Africa
Cape Verde Africa
Central African Republic Africa
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:
proc sql outobs=12; title 'World Topographical Features'; select Name, Type from sql.features order by Type desc, Name;
Note: The ASC keyword is optional
because the PROC SQL default sort order is ascending.
World Topographical Features
Name Type
---------------------------
Angel Falls Waterfall
Niagara Falls Waterfall
Tugela Falls Waterfall
Yosemite Waterfall
Andaman Sea
Baltic Sea
Bering Sea
Black Sea
Caribbean Sea
Gulf of Mexico Sea
Hudson Bay Sea
Mediterranean Sea
| 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:
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;
World Population Densities per Square Mile
Name Population Area Density
-----------------------------------------------------------------------
Hong Kong 5,857,414 400 14,644
Singapore 2,887,301 200 14,437
Luxembourg 405,980 100 4,060
Malta 370,633 100 3,706
Maldives 254,495 100 2,545
Bangladesh 126,387,850 57,300 2,206
Bahrain 591,800 300 1,973
Taiwan 21,509,839 14,000 1,536
Channel Islands 146,436 100 1,464
Barbados 258,534 200 1,293
Korea, South 45,529,277 38,300 1,189
Mauritius 1,128,057 1,000 1,128
| 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:
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.
World Population Densities per Square Mile
Name Population Area Density
-----------------------------------------------------------------------
Hong Kong 5,857,414 400 14,644
Singapore 2,887,301 200 14,437
Luxembourg 405,980 100 4,060
Malta 370,633 100 3,706
Maldives 254,495 100 2,545
Bangladesh 126,387,850 57,300 2,206
Bahrain 591,800 300 1,973
Taiwan 21,509,839 14,000 1,536
Channel Islands 146,436 100 1,464
Barbados 258,534 200 1,293
Korea, South 45,529,277 38,300 1,189
Mauritius 1,128,057 1,000 1,128
| Sorting by Columns That Are Not Selected |
You can sort query results by columns that are not included in the query. For example, the following query returns all the rows in the SQL.COUNTRIES table and sorts them by population, even though the Population column is not included in the query:
proc sql outobs=12; title 'Countries, Sorted by Population'; select Name, Continent from sql.countries order by Population;
Sorting by Columns That are not Selected
Countries, Sorted by Population
Name Continent
------------------------------------------------------------------------
Vatican City Europe
Tuvalu Oceania
Nauru Oceania
Turks and Caicos Islands Central America and Caribbean
Leeward Islands Central America and Caribbean
Cayman Islands Central America and Caribbean
San Marino Europe
Liechtenstein Europe
Gibraltar Europe
Monaco Europe
Saint Kitts and Nevis Central America and Caribbean
Marshall Islands Oceania
| Specifying a Different Sorting Sequence |
SORTSEQ= is a PROC SQL statement option that specifies the sorting sequence for PROC SQL to use when a query contains an ORDER BY clause. Use this option only if you want to use a sorting sequence other than your operating environment's default sorting sequence. Possible values include ASCII, EBCDIC, and some languages other than English. For example, in an operating environment that supports the EBCDIC sorting sequence, you could use the following option in the PROC SQL statement to set the sorting sequence to EBCDIC:
proc sql sortseq=ebcdic;
Note: SORTSEQ= affects only the ORDER
BY clause. It does not override your operating environment's default comparison
operations for the WHERE clause.
Operating Environment Information: See the SAS
documentation for your operating environment for more information about the
default and other sorting sequences for your operating environment.
| Sorting Columns That Contain Missing Values |
PROC SQL sorts nulls, or missing values, before character or numeric data; therefore, when you specify ascending order, missing values appear first in the query results.
The following example sorts the rows in the CONTINENTS table by the LowPoint column:
proc sql; title 'Continents, Sorted by Low Point'; select Name, LowPoint from sql.continents order by LowPoint;
Because three continents have a missing value in the LowPoint column, those continents appear first in the output. Note that because the query does not specify a secondary sort, rows that have the same value in the LowPoint column, such as the first three rows of output, are not displayed in any particular order. In general, if you do not explicitly specify a sort order, then PROC SQL output is not guaranteed to be in any particular order.
Sorting Columns That Contain Missing Values
Continents, Sorted by Low Point
Name LowPoint
------------------------------------------------------------------------
Central America and Caribbean
Antarctica
Oceania
Europe Caspian Sea
Asia Dead Sea
North America Death Valley
Africa Lake Assal
Australia Lake Eyre
South America Valdes Peninsula
|
|
Copyright © 2008 by SAS Institute Inc., Cary, NC, USA. All rights reserved.