COUNT Function
Returns the number of rows retrieved by a SELECT statement for a specified table.
| Categories: | Aggregate |
|---|---|
| Descriptive Statistics | |
| Alias: | FREQ, N |
| Restriction: | Aggregate functions take one argument. When more than one argument is used within a SQL aggregate function, the function is no longer considered to be a SQL aggregate or summary function and a like-named SAS function is used instead. See COUNT Function in SAS Functions and CALL Routines: Reference. |
| Returned data type: | Numeric |
Table of Contents
Syntax
Required Arguments
*
returns a count of all rows in a table or grouping of rows, including rows that contain missing values.
sql-expression
specifies any valid SQL expression. See sql-expression Component.
Optional Arguments
ALL
(default) specifies that all nonmissing values returned by sql-expression be counted.
DISTINCT
eliminates duplicate rows returned by sql-expression from the count.
| Note | Do not use
select count(distinct *) to count distinct
rows in a table. This code generates an error because PROC SQL does
not know which duplicate column values to eliminate. |
|---|
Details
The COUNT function has three forms:
- Form 1: COUNT(*)
-
counts the number of rows in a table, including those that contain missing values.
- Form 2: COUNT(expression)
-
counts the number of rows in sql-expression that have a nonmissing value.
- Form 3: COUNT(DISTINCT expression)
-
counts the number of rows in expression that have unique values. SAS missing values are included in the result.
When used with the GROUP BY clause, each form returns the number of all values that meet the specified condition for each group that is specified in a GROUP BY clause.
When form 1 is used with a GROUP BY clause, the results include a grouping of missing values. You can use a HAVING clause to filter the rows that are returned by COUNT(*). For an example, see Grouping and Sorting Data.
When the SELECT clause of a table expression contains one or more summary functions and that table expression resolves to no rows, COUNT(*) and COUNT(DISTINCT sql-expression) return a zero. COUNT(expression) returns a missing value.
For more examples, see Creating a View from a Query’s Result, Using Aggregate Functions with Unique Values, and Counting Missing Values with a SAS Macro.