SAS 9.4 SQL Procedure User's Guide, Second Edition, 2nd Edition

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