SAS/ACCESS Interface to SYSTEM 2000 Data Management Software: Reference

Missing Values (Nulls)

SYSTEM 2000 software and the SAS System handle missing values (nulls) differently. The interface view engine that stands between the two systems must handle the differences in a predictable, useful way. The following topics discuss how missing values and empty records are handled in retrieval, update, and where-clause processing.

Retrieving Nulls

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

missing structure means the data record at the top of the view exists, but some or all of its descendant records do not exist.
missing values means that the value for one or more items in a data record is null. Nulls for all item types consist of all binary zeros in the database.

In the SAS System, missing values in character variables are represented by all blanks. Missing numeric values are represented by a special floating point number.

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

Updating Nulls

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

ADD means adding an observation, which can have missing values. The interface view engine converts the SAS observation into a set of one or more SYSTEM 2000 data records, comprising the path defined by the view descriptor. Each variable in each record is converted from the SAS internal format to the SYSTEM 2000 format. Note that even if all variables in a SYSTEM 2000 record have missing values, the record will still be inserted into the database. That is, the complete path of data records is always inserted; lower level data records might contain all missing values.

UPDATE means updating an observation with a set of values, which can have missing values. 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 will be updated with whatever values have changed since the path was retrieved. Missing structures will be inserted only if the values are not null.

DUP means duplicating the selected observation in the database, which could mean duplication of more than one database record.

DELETE means deleting an observation, which could mean deletion of more than one database record. For more information about deletions, see Deleting Data Records. Because SYSTEM 2000 software preserves all blanks for TEXT and UNDEFINED values, a value containing all blanks for one of these item types would be interpreted as a missing value by a SAS procedure.

Missing Values in Selection Criteria

SYSTEM 2000 software and the SAS System treat missing values (nulls) differently when processing where-clause conditions. SYSTEM 2000 software 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; the NE (not equal) operator will not qualify nulls. Also, a null will never satisfy a condition containing the LT (less than) or LE (less than or equal) operator. For example, if item C2 is null in some data records, the following item-to-item condition will never qualify those records, regardless of the respective values:

  WHERE C1* > C2*

In fact, for any relational operator in an item-to-item condition, SYSTEM 2000 software 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 will not qualify. In contrast, the SAS System assumes null fields (missing values) are equal to each other. Missing values have the following attributes in the SAS System:

For numeric variables, a missing value is the lowest (most negative) possible value. It is lower than every other value that is not null.
For character variables, a value of all blanks is considered to be a missing value.

When the SAS System processes a condition such as C1 >= C2, the qualified records would include every record in which C2 is missing, regardless of the value of C1. Also, the condition C1 = C2 would qualify records that have missing values for both C1 and C2, along with records where C1 and C2 have equal values that are not null.

Because of these different treatments, it can be important to know whether the SAS System or SYSTEM 2000 software is processing a particular portion of a where-clause. Where-clause processing is discussed in Using a SAS WHERE Clause for Selection Criteria. Briefly, the where-clause in a view descriptor is never seen by the SAS System and will be processed by SYSTEM 2000 software. However, the WHERE clause associated with the SAS procedure, the DATA step, or a SELECT statement in the SQL procedure can be processed partly by both the SAS System and SYSTEM 2000 software, depending upon whether individual conditions are meaningful to SYSTEM 2000 software.

Since missing values are different, a condition in a SAS WHERE clause that explicitly uses the dot (.) notation will never be seen by SYSTEM 2000 software. The SAS System will perform the qualification for such conditions. For more information about where-clause processing, read the next sections.

Chapter Contents
Previous
Next
Top of Page