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

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

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