NMISS Function

Returns the number of null values or SAS missing values in an expression.

Categories: Aggregate
Scalar
Syntax
Arguments
Details
Comparisons
Examples
Example 1: Aggregate Operation
Example 2: Scalar Operation
See Also

Syntax

Aggregate:
NMISS(expression)
Scalar:
NMISS(expression[, ...expression])

Arguments

expression (aggregate)

specifies a SQL expression that evaluates to a character column.

Data type CHAR
Returned data type BIGINT

expression (scalar)

specifies a SQL expression that evaluates to a numeric value. Multiple expressions must be separated by commas.

Data type DECIMAL, DOUBLE, NUMERIC
Returned data type INTEGER
See <sql-expression>
FedSQL Expressions

Details

NMISS returns the number of null or SAS missing values. When FedSQL is run in ANSI mode, ANSI null values are converted to SAS missing values.
When used as an aggregate function, NMISS returns the number of null or missing values found in a single character column. You can use an aggregate function to produce a statistical summary of data in the entire table that is listed in the FROM clause or for each group that is specified in a GROUP BY clause. The GROUP BY clause groups data by a specified column or columns. When you use a GROUP BY clause, the aggregate function in the SELECT clause or in a HAVING clause instructs FedSQL in how to summarize the data for each group. FedSQL calculates the aggregate function separately for each group. If GROUP BY is omitted, then all the rows in the table or view are considered to be a single group.
When used as a scalar function, NMISS returns the number of null or missing values among the specified expression(s).

Comparisons

The NMISS function returns the number of null or SAS missing values. The N function returns the number of non-null and non-missing values.

Examples

Example 1: Aggregate Operation

The following statement illustrates the use of NMISS as an aggregate function.
Statements
Results
 
select nmiss(AvgHigh) from worldtemps;
 
1

Example 2: Scalar Operation

The following statement illustrates the use of NMISS as a scalar function:
Statements
Results
 
select nmiss(1,2,.,3,.);
 
2

See Also

How FedSQL Processes Nulls and SAS Missing Values
Functions:
N Function
SELECT Statement Clauses:
SELECT Clause
GROUP BY Clause
HAVING Clause
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: February 23, 2017