Compare Incomplete or Mismatched Data

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 Special-Case 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.

Note: Matrices must include the same number of columns or rows.

Some Special-Case Comparison Tests
Test	Result	Explanation
m=.; m==1	.	An equality test with a missing value returns missing.
m=.; m!=1	.	An inequality test with a missing value returns missing.
m=.; m<1; m>1; and so on	.	A comparison with a missing value returns missing (unless it could not possibly be true, see next).
m=.; 1<m<0	0	A comparison involving a missing value that could not possibly be true returns false; false takes precedence over missing for comparisons with more than two arguments (as with logical operators).
{a, b}==list(a, b)	1	An equality test of list arguments returns a single result.
{a, b}<{a, c}	.	A comparison test of list arguments is not allowed.
1=="abc"	0	An equality test with mixed data types returns false.
1<="abc"	.	A comparison with mixed data types returns missing.
[1 2 3]==[2 2 5]	[0 1 0]	An equality test of matrices returns a matrix of elementwise results. When a matrix is compared to a matrix, comparison is done element-by-element and returns a matrix of 1s and 0s.
[1 2 3]==2	[0 1 0]	An equality test of a matrix and a matrix filled with 2s. If a matrix is compared to a number, the number is treated as a matrix filled with that number.
[1 2 3] < [2 2 5]	[1 0 1]	A comparison of matrices returns a matrix of elementwise results.
[1 2 3] < 2	[1 0 0]	A comparison of a matrix and a matrix filled with 2s.
Is Missing(m)	1	Returns 1 for a missing value and returns 0 otherwise. For missing character values, you can also use empty quotes for the comparison, as in m == "".
Zero Or Missing(m)	1	Returns 1 when the value is 0 or missing. The argument must be numeric or a matrix and not a string.
All([2 2]==[1 2])	0	Summarizes elementwise comparisons; returns 1 only if all comparisons are true and returns 0 otherwise.
Any([2 2]==[1 2])	1	Summarizes elementwise comparisons; returns 1 if any comparison is true and returns 0 otherwise.

Missing Values

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:

New Table( "Testing Comparisons",

Format( "Best", 10 ),

Set Values( [1, 2, 3, .] )

New Column( "B", Character, "Nominal",

The following values are stored in column B:

"true"

"false"

The script works like this:


If(	Begins the comparison.
:A, "true",	If the value of A is nonmissing and nonzero, the result is "true". This comparison is true for the first three rows.
1, "false"	The value of 1 is always true, so the missing value returns "false".
)	Closes the comparison.

The two exceptions to this rule involve comparing a missing value to a known value:

•	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.)

Is Missing

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:

If( :A, "true", Is Missing( :A ), "missing", "false" );

The preceding expression returns "true" when A is nonmissing and nonzero, "missing" when A is missing, and "false" otherwise.

Zero Or Missing

If the missing value could be 0, use the Zero Or Missing() function instead:

Zero Or Missing(A);

This expression returns 1 when A is 0 or missing.

Tip: You cannot compare a known value with an explicitly defined missing value, only with variables, matrices, or other things that could contain missing values.