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.31 Using a WHERE Clause with Missing Values (Corrected Output)
Summarizing Data
Overview of Summarizing Data
You can use an aggregate function (or summary function) to produce a statistical
summary of data in a table. The aggregate function instructs PROC SQL in how to
combine data in one or more columns. If you specify one column as the argument to an
aggregate function, then the values in that column are calculated. If you specify multiple
arguments, then the arguments or columns that are listed are calculated.
Note: When more than one argument is used within an SQL aggregate function, the
function is no longer considered to be an SQL aggregate or summary function. If
there is a like-named Base SAS function, then PROC SQL executes the Base SAS
function and the results that are returned are based on the values for the current row.
If no like-named Base SAS function exists, then an error will occur. For example, if
you use multiple arguments for the AVG function, an error will occur because there
is no AVG function for Base SAS.
When you use an aggregate function, PROC SQL applies the function to the entire table,
unless you use a GROUP BY clause. You can use aggregate functions in the SELECT or
HAVING clauses.
Note: See “Grouping Data” on page 64 for information about producing summaries of
individual groups of data within a table.
Using Aggregate Functions
The following table lists the aggregate functions that you can use:
Table 2.6 Aggregate Functions
Function Definition
AVG, MEAN mean or average of values
COUNT, FREQ, N number of nonmissing values
56 Chapter 2 Retrieving Data from a Single Table
Function Definition
CSS corrected sum of squares
CV coefficient of variation (percent)
MAX largest value
MIN smallest value
NMISS number of missing values
PRT probability of a greater absolute value of
Student's t
RANGE range of values
STD standard deviation
STDERR standard error of the mean
SUM sum of values
SUMWGT
sum of the WEIGHT variable values
1
T
Student's t value for testing the hypothesis
that the population mean is zero
USS uncorrected sum of squares
VAR variance
Note: You can use most other SAS functions in PROC SQL, but they are not treated as
aggregate functions.
Summarizing Data with a WHERE Clause
Overview of Summarizing Data with a WHERE Clause
You can use aggregate, or summary functions, by using a WHERE clause. For a
complete list of the aggregate functions that you can use, see Table 2.6 on page 56.
Using the MEAN Function with a WHERE Clause
This example uses the MEAN function to find the annual mean temperature for each
country in the Sql.WorldTemps table. The WHERE clause returns countries with a mean
temperature that is greater than 75 degrees.
libname sql 'SAS-library';
proc sql outobs=12;
1
In the SQL procedure, each row has a weight of 1.
Summarizing Data 57
title 'Mean Temperatures for World Cities';
select City, Country, mean(AvgHigh, AvgLow)
as MeanTemp
from sql.worldtemps
where calculated MeanTemp gt 75
order by MeanTemp desc;
Note: You must use the CALCULATED keyword to reference the calculated column.
Output 2.32 Using the MEAN Function with a WHERE Clause
Displaying Sums
The following example uses the SUM function to return the total oil reserves for all
countries in the Sql.OilRsrvs table:
libname sql 'SAS-library';
proc sql;
title 'World Oil Reserves';
select sum(Barrels) format=comma18. as TotalBarrels
from sql.oilrsrvs;
Note: The SUM function produces a single row of output for the requested sum because
no nonaggregate value appears in the SELECT clause.
58 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