#NOTIN
This operator asks if the left argument, a database field, does not contain any of the values listed in the right argument. 1 is returned if the field value does not occur in the list; otherwise, 0 is returned.
Syntax:
database field #NOTIN List
Examples:
If the database field type is coded or numeric, then List is a parenthesized list of numbers separated by commas:
STATUS #NOTIN (1,2,9)
PAYRATE #NOTIN (17.9,18.2)
Note that for coded fields, you must use the field’s numeric codes (which are shown in the F1 expression help) rather than the character labels for the field.
If the database field type is social security number, then List may, optionally, include dashes:
SSN #NOTIN (756-45-3432,156-47-5364,345-36-4554) or
SSN #NOTIN (756453432,156475364,345364554)
If the database field type is date, then List is a parenthesized list of dates:
DOR #NOTIN (1/1/1990,2/15/1989,12/1/2008)
If the database field type is character, then List is a single pattern (i.e., a string of text surrounded by single quotes) or a parenthesized list of patterns. Character fields can be compared to patterns of specific character values (‘SMITH’,’JONES’) or patterns containing special characters (‘SMI*’,’JO*’). For example,
NAME #NOTIN ‘SMITH’
NAME #NOTIN (‘SMITH’,’JONES’)
NAME #NOTIN ('SMI*’,’J*’,’*ERS')
The first expression selects records for which the Name field does not contain the value SMITH; in other words, the expression returns 1 when Name is not SMITH and 0 when it is. The second expression selects records for which the Name field does not contain either the value SMITH or the value JONES. The third expression selects records for which the NAME field does not contain a value among the patterns of the list. For details about special characters, such as the one (*) in our example, see the discussion of patterns.
Case differences are ignored (‘SMITH’ vs. ‘Smith’ or ‘smith’).
If the right argument to #NOTIN is empty (just two adjacent single quotes, perhaps separated by a blank space), then records without blank field values are selected. That is, the patterns ‘’ and ‘ ‘ match a string of blank characters. This is how you select non-missing values of a character field. Note that the double quote character (“) cannot be used as a substitute for two adjacent single quotes.
If a pattern in the right argument contains a single quote, you must double it in the argument to #IN, as in ‘That”s the ticket’. Double quote characters have to be quadrupled in strings; see the discussion of patterns.
Related Operators: