Handling Null Values in Expressions

When data does not exist for a variable, Maintain Data assigns the following default value, depending on how the format of the variable has been defined:

Data Type

Default value without the MISSING attribute

Default value with the MISSING attribute

Numeric

zero (0)

null

Date and time

space

null

Character

space

null

A null value (sometimes known as missing data) appears as a period (.) by default. You can change the character representation of the null value by issuing the SET NODATA command. For details, see the ibi™ WebFOCUS® Developing Reporting Applications guide.

Null values affect the results of expressions that perform aggregating calculations such as averaging and summing. For information about the MISSING attribute in Master Files and the effect of null values in calculations, see the topics about null data and missing data in Handling Null Values in Expressions.

Assigning Null Values: The MISSING Constant

You can assign the MISSING constant (that is, the null value) to variables (data source fields and temporary variables) that were defined with the MISSING attribute.

When you create a user-defined variable with the MISSING attribute and do not explicitly assign a value, it is created with the null value. For example, in the following command, Name is created with a null value:

COMPUTE Name/A15 MISSING ON = ;

Assign Null Values: The MISSING Constant

The syntax for assigning a null value to an existing variable is:

COMPUTE target_variable = MISSING;

Assigning Null Values

Suppose that the variable AcctBalance had been defined with the MISSING attribute. The command below assigns the null value to AcctBalance: 

COMPUTE AcctBalance = MISSING;

Conversion in Mixed-Format Null Expressions

When a variable with a null value is assigned to a variable that is not defined with the MISSING attribute, the null value is converted to a zero or a space. For example, when the variable Q is assigned to R, the null value from Q is converted to a zero, because zero is the default value for numeric variables without the MISSING attribute. 

Q/I4 MISSING ON = MISSING;
R/I4 = Q;

The same conversion occurs before any mathematical operations are applied if the variables are used as operands in arithmetic expressions.

Testing Null Values

You may test for the null value using the comparison operators EQ or NE in an expression. You can test any variable that has been declared with the MISSING attribute. The null value is represented by the MISSING constant.

Test Null Values

The syntax for testing whether a value is null is:

target_variable {EQ|NE} MISSING

Testing Null Values

In this example, an IF command executes a BEGIN block if the variable Returns is null:

IF Returns EQ MISSING THEN BEGIN
.
.
.
ENDBEGIN