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

Output 2.46 Adjusting the Query to Avoid Errors Due to Missing Values (Modified Output)
Note: Aggregate functions, such as the SUM function, can cause the same calculation to
repeat for every row. This occurs whenever PROC SQL remerges data. For more
information about remerging, see “Remerging Summary Statistics” on page 59.
Filtering Grouped Data
Overview of Filtering Grouped Data
You can use a HAVING clause with a GROUP BY clause to filter grouped data. The
HAVING clause affects groups in a way that is similar to how a WHERE clause affects
individual rows. When you use a HAVING clause, PROC SQL displays only the groups
that satisfy the HAVING expression.
Using a Simple HAVING Clause
The following example groups the features in the Sql.Features table by type and then
displays only the numbers of islands, oceans, and seas:
libname sql 'SAS-library';
proc sql;
title 'Numbers of Islands, Oceans, and Seas';
select Type, count(*) as Number
from sql.features
group by Type
Filtering Grouped Data 69
having Type in ('Island', 'Ocean', 'Sea')
order by Type;
Output 2.47 Using a Simple HAVING Clause
Choosing between HAVING and WHERE
The differences between the HAVING clause and the WHERE clause are shown in the
following table. Because you use the HAVING clause when you work with groups of
data, queries that contain a HAVING clause usually also contain the following:
a GROUP BY clause
an aggregate function
T I P A HAVING clause is like a WHERE clause for groups.
Note: If you use a HAVING clause without a GROUP BY clause and if the query
references at least one aggregate function, PROC SQL treats the input data as if it all
comes from a single group of data.
Table 2.7 Differences between the HAVING Clause and WHERE Clause
HAVING clause attributes WHERE clause attributes
is typically used to specify conditions for
including or excluding groups of rows from a
table.
is used to specify conditions for including or
excluding individual rows from a table.
must follow the GROUP BY clause in a
query, if used with a GROUP BY clause.
must precede the GROUP BY clause in a
query, if used with a GROUP BY clause.
is affected by a GROUP BY clause, when
there is no GROUP BY clause, the HAVING
clause is treated like a WHERE clause.
is not affected by a GROUP BY clause.
is processed after the GROUP BY clause and
any aggregate functions.
is processed before a GROUP BY clause, if
there is one, and before any aggregate
functions.
Using HAVING with Aggregate Functions
The following query returns the populations of all continents that have more than 15
countries:
70 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