Overview (STAR JOIN command)
STAR JOIN performs the equivalent of an SQL left outer join. The FROM subcommand specifies the case data file (fact table). The JOIN subcommands specify the table lookup files (dimension tables).
- All cases (records) from the case data file are included in the merged file.
- Cases from the table lookup files are included only if they have key values that match key values in the case data file.
- The merged file contains all fields specified on the SELECT subcommand and all fields specified as keys on the JOIN subcommand(s).
This is similar to MATCH FILES with one or more TABLE lookup subcommands, but STAR JOIN provides several features not available in MATCH FILES:
- The files do not have to be presorted in order of the key values.
- Different keys can be specified for each table lookup file.
- The defined width of string keys does not have to be the same in both the case data file and the table lookup file.
Note: Unlike MATCH FILES, STAR JOIN reads the active dataset and causes execution of any pending transformations.
Syntax
- All subcommands except PASSPROTECT are required.
- All subcommands must be in the order indicated in the syntax chart: SELECT, FROM, JOIN, OUTFILE, PASSPROTECT.
- Key values in files specified on the JOIN subcommand 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.
Operations
The merged dataset contains complete dictionary information from the input files, including variable names, labels, print and write formats, and missing-value indicators.
- For key variables or other variables with the same name in more than one file, variable dictionary information is taken from the first file containing value labels, missing values, or a variable label for the common variable. If the case data file has no such information, the first lookup table specified is checked, and so on. This includes custom variable attributes.
- Custom file attributes (DATAFILE ATTRIBUTES command) are taken from the first file that contains custom file attributes. If the case data file has none, the first table lookup file is checked, and so on.
- The merged file includes all documents from all files (documents are created with the DOCUMENT and ADD DOCUMENTS commands).
- No file labels (FILE LABEL command) from any of the files are preserved.