JOIN subcommand (STAR JOIN command)

The JOIN subcommand specifies the table lookup file (dimension table). It is followed by the keyword AS, followed by the alias used to identify the file source when specifying fields, followed by the keyword ON, which specifies the key(s) to use to match the file with the case data file.

• At least one JOIN subcommand is required, and all JOIN subcommands must follow the FROM subcommand.
• The maximum number of JOIN subcommands is determined by the operating system limit for file handles or available memory.
• The data file name can be an external data file in IBM® SPSS® Statistics format or a dataset in the current session.
• External data file names must be quoted.
• An asterisk can be used to specify the currently active dataset.
• Alias names must follow standard variable naming rules. See the topic Variable Names for more information.
• To specify the same lookup table on multiple JOIN subcommands, use different alias names for each instance.
• Key values within each file must be unique. Duplicate key values within the same file will result in an error. If there are multiple keys, each combination of keys must be unique.

ON Keyword

The ON keyword specifies the keys(s) used to match cases in the table lookup file with cases in the case data file. The general form is:

ON alias0.field=alias1.field 

• Each key or key combination that identifies each case must be unique in the table lookup file.
• The basic data type (string or numeric) for each key comparison must be the same in both files.
• To specify multiple keys that identify each unique case in the table lookup file, use the keyword AND, as in:

  ON alias0.field1=alias1.field1 AND alias0.field2=alias1.field2

• The field names do not have to be the same in the two files. If they are different, both fields are included in the merged data file.
• The only comparison that is allowed is equality between field values. Other comparisons (for example, >=, <=, <>) are not allowed, and arithmetic expressions (for example, t1.field1 = t2.field1 + t2.field2) are not allowed.
• Comparisons can only be evaluated between the current table lookup file and the case data file. For example, if the case data file is t1, and you have two lookup files, t2 and t3, ON t2.field1=t3.field1 is not valid.

IN Keyword

The optional IN keyword creates a new variable (field) in the merged file that indicates if the case (based on the key values) is present in that table lookup file. A value 1 indicates that the case is present; a value of 0 indicates that it is not. You can use this to identify cases in the case data file that have no matching cases in the table lookup file.

The keyword is followed by an equals sign (=) and the name of the new variable. The name must conform to variable naming rules.