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

Summarizing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Overview of Summarizing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Using Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Summarizing Data with a WHERE Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Displaying Sums . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

Combining Data from Multiple Rows into a Single Row . . . . . . . . . . . . . . . . . . . . 59

Remerging Summary Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59

Using Aggregate Functions with Unique Values . . . . . . . . . . . . . . . . . . . . . . . . . . . 61

Summarizing Data with Missing Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62

Grouping Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Grouping by One Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Grouping without Summarizing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Grouping by Multiple Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Grouping and Sorting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66

Grouping with Missing Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67

Filtering Grouped Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

Overview of Filtering Grouped Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

Using a Simple HAVING Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

Choosing between HAVING and WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

Using HAVING with Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70

Validating a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

Overview of the SELECT Statement

How to Use the SELECT Statement

This chapter shows you how to perform the following tasks:

• retrieve data from a single table by using the SELECT statement

• validate the correctness of a SELECT statement by using the VALIDATE statement

With the SELECT statement, you can retrieve data from tables or data that is described

by SAS data views.

Note: The examples in this chapter retrieve data from tables that are SAS data sets.

However, you can use all of the operations that are described here with SAS data

views.

The SELECT statement is the primary tool of PROC SQL. You use it to identify,

retrieve, and manipulate columns of data from a table. You can also use several optional

clauses within the SELECT statement to place restrictions on a query.

SELECT and FROM Clauses

The following simple SELECT statement is sufficient to produce a useful result:

select Name

from sql.countries;

The SELECT statement must contain a SELECT clause and a FROM clause, both of

which are required in a PROC SQL query. This SELECT statement contains the

following:

• a SELECT clause that lists the Name column

20 Chapter 2 • Retrieving Data from a Single Table