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

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

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