Comparing data that contains missing values can return misleading results unless you specify a condition that is always true or use functions such as Is Missing() or Zero Or Missing(). Comparisons of data with mismatched types (numeric versus character) or data in matrices can also be confusing.
Some SpecialCase Comparison Tests shows examples of such comparisons and matrices and explanations of the results. For a review of operators used in comparisons, see Operators. The sections that follow the table provide more details about comparison and logical operators.
Summarizes elementwise comparisons; returns 1 only if all comparisons are true and returns 0 otherwise.


Summarizes elementwise comparisons; returns 1 if any comparison is true and returns 0 otherwise.

In a comparison, missing values typically return missing, not true or false. For this reason, it is very important to include a result that is always true. Suppose that a data table column contains the values 1, 2, 3, and a missing value in column A. A formula in column B sets up the comparison. For example, the following script:
If the value of A is nonmissing and nonzero, the result is "true". This comparison is true for the first three rows.


•

If one value is true and another is missing, Or() returns true. (Only one value in an Or() test needs to be true to get a true result.)

•

If one value is false and another is missing, And() returns false. (Both values in an And() test must be true to get a true result.)

If you know that some values are missing, you can also compare with Is Missing(). The comparison in the preceding example can be rewritten to return "missing" for missing values:
The preceding expression returns "true" when A is nonmissing and nonzero, "missing" when A is missing, and "false" otherwise.
If the missing value could be 0, use the Zero Or Missing() function instead:
This expression returns 1 when A is 0 or missing.