Missing Values (Nulls)

Retrieving Nulls

When the interface view engine is reading database records and constructing an observation, it might find that data is missing in the path of the data records that represent the observation.

In a SYSTEM 2000 database,

missing structure means that the data record at the highest level of the view exists, but some or all of its descendant records do not exist.
missing values (nulls) means that the values for one or more items in a data record do not exist. Nulls for all item types are represented by binary zeros in the database.

In SAS,

missing values in character variables are represented by blanks
missing numeric values are represented by a period (.)

When the interface view engine retrieves a null from the database, it sets the null as a missing value in the corresponding SAS observation. Because SYSTEM 2000 preserves all blanks for TEXT and UNDEFINED values, a value that contains all blanks for one of these item types is interpreted as a missing value by a SAS procedure.

Updating Nulls

The interface view engine supports four types of updates: ADD, UPDATE, DUP, and DELETE.

ADD

adds an observation, which can have nulls. The interface view engine converts a SAS observation into a set of one or more SYSTEM 2000 data records, which comprise the path defined by the view descriptor. Each variable in each record is converted from the SAS internal format to the SYSTEM 2000 format. Even if all variables in a SYSTEM 2000 record have nulls, the record is inserted into the database. That is, the complete path of data records is always inserted; lower-level data records might contain all nulls.

UPDATE

updates an observation in a record with a set of values. The record might contain nulls.

If the observation being updated has no missing structure, each variable is converted from its SAS form into a SYSTEM 2000 form.

If the observation being updated has a missing structure in the database, the records that exist in the path are updated with whatever values have changed since the path was retrieved. Missing structures will be inserted only if the values are not null.

DUP

duplicates the selected observation in the database, which can cause duplication of more than one database record.

DELETE

deletes an observation, which can cause deletion of more than one database record. For more information, see Deleting Data Records.

Nulls in Selection Criteria

SYSTEM 2000 and SAS treat nulls differently when processing where-clause conditions. SYSTEM 2000 assumes that a null is outside the domain of values for an item. Therefore, the only way to qualify a null is by using the FAILS operator. In fact, for any relational operator in an item-to-item condition, SYSTEM 2000 never qualifies a record in which either of the items is null. Even if the condition is C1* = C2* and both items are null, the record does not qualify. For example, if item C2 is null in some data records, the following item-to-item condition never qualifies those records, regardless of the respective values:

  WHERE C1* > C2*

In contrast, SAS assumes that nulls are equal to each other. In SAS, nulls

for numeric variables are indicated by periods
for character variables are indicated by blanks

When SAS processes a condition such as C1 >= C2, the qualified records include every record in which C2 is null, regardless of the value of C1. Also, the condition C1 = C2 qualifies records that have nulls for both C1 and C2, in addition to records in which C1 and C2 have equal values that are not null.

Because of these different treatments, it is important to know whether SAS or SYSTEM 2000 is processing a where-clause. The where-clause in a view descriptor is never seen by SAS and is processed by SYSTEM 2000. However, the WHERE clause associated with a SAS procedure, the DATA step, or a SELECT statement in the SQL procedure can be processed partly by both SAS and SYSTEM 2000 if individual conditions are meaningful to SYSTEM 2000.

Because missing values are different, a condition in a WHERE clause in SAS that uses the period (.) notation is never seen by SYSTEM 2000. SAS performs the qualification for such conditions. For more information, see WHERE Clauses in SAS and where-clauses in SYSTEM 2000.