When the data contains null values, you might get different result sets depending
on whether the processing is done in SAS
missing value mode or in
ANSI SQL null value mode. Although in many cases a difference in mode does not present a problem, it
is important to understand how these differences occur.
Processing SAS missing values is different from processing ANSI SQL null values and
has significant implications in these situations:
-
when filtering data (for example,
in a WHERE clause, a HAVING clause, or an outer join ON clause). SAS
mode interprets null values as SAS missing values, which are known
values, whereas ANSI mode interprets null values as unknown values.
-
when submitting outer joins where
internal processing might generate nulls for intermediate result sets.
-
when comparing a blank character, SAS mode interprets the blank character as a missing
value. In ANSI mode, a blank character is a blank character; it has no special meaning.