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

from sql.countries

group by Continent

having Continent in ('Asia', 'Europe')

order by Continent;

Ordering the SELECT Statement

When you construct a SELECT statement, you must specify the clauses in the following

order:

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY

Note: Only the SELECT and FROM clauses are required.

The PROC SQL SELECT statement and its clauses are discussed in further detail in the

following sections.

Selecting Columns in a Table

When you retrieve data from a table, you can select one or more columns by using

variations of the basic SELECT statement.

Selecting All Columns in a Table

Use an asterisk in the SELECT clause to select all columns in a table. The following

example selects all columns in the Sql.USCityCoords table, which contains latitude and

longitude values for U.S. cities:

libname sql 'SAS-library';

proc sql outobs=12;

title 'U.S. Cities with Their States and Coordinates';

select *

from sql.uscitycoords;

Note: The OUTOBS= option limits the number of rows (observations) in the output.

OUTOBS= is similar to the OBS= data set option. OUTOBS= is used throughout

this document to limit the number of rows that are displayed in examples.

Note: In the tables used in these examples, latitude values that are south of the Equator

are negative. Longitude values that are west of the Prime Meridian are also negative.

22 Chapter 2 • Retrieving Data from a Single Table

Output 2.1 Selecting All Columns in a Table

Note: When you select all columns, PROC SQL displays the columns in the order in

which they are stored in the table.

Selecting Specific Columns in a Table

To select a specific column in a table, list the name of the column in the SELECT clause.

The following example selects only the City column in the Sql.USCityCoords table:

libname sql 'SAS-library';

proc sql outobs=12;

title 'Names of U.S. Cities';

select City

from sql.uscitycoords;

Selecting Columns in a Table 23

Output 2.2 Selecting One Column

If you want to select more than one column, then you must separate the names of the

columns with commas, as in this example, which selects the City and State columns in

the Sql.USCityCoords table:

libname sql 'SAS-library';

proc sql outobs=12;

title 'U.S. Cities and Their States';

select City, State

from sql.uscitycoords;

24 Chapter 2 • Retrieving Data from a Single Table

Output 2.3 Selecting Multiple Columns

Note: When you select specific columns, PROC SQL displays the columns in the order

in which you specify them in the SELECT clause.

Eliminating Duplicate Rows from the Query Results

In some cases, you might want to find only the unique values in a column. For example,

if you want to find the unique continents in which U.S. states are located, then you might

begin by constructing the following query:

libname sql 'SAS-library';

proc sql outobs=12;

title 'Continents of the United States';

select Continent

from sql.unitedstates;

Selecting Columns in a Table 25

Output 2.4 Selecting a Column with Duplicate Values

You can eliminate the duplicate rows from the results by using the DISTINCT keyword

in the SELECT clause. Compare the previous example with the following query, which

uses the DISTINCT keyword to produce a single row of output for each continent that is

in the Sql.UnitedStates table:

libname sql 'SAS-library';

proc sql;

title 'Continents of the United States';

select distinct Continent

from sql.unitedstates;

Output 2.5 Eliminating Duplicate Values

Note: When you specify all of a table's columns in a SELECT clause with the

DISTINCT keyword, PROC SQL eliminates duplicate rows, or rows in which the

values in all of the columns match, from the results.

26 Chapter 2 • Retrieving Data from a Single Table