Add Variables

Merge Method tab

Use the Merge Method tab to define the merge type.

One-to-one merge based on file order

Order of cases in the files determines how cases are matched. This is the default setting when no variables exist with the same name and basic type (string or numeric) in both files. This setting generates MATCH FILES command syntax in the following format (where [name] is either a dataset or external file specification (enclosed in quotation marks)):

MATCH FILES FILE=* /FILE="[name]"

One-to-one merge based on key values

Cases are matched based on the values of one or more key variables. This is the default setting when one or more variables have the same name and basic type (string or numeric) in both files. This setting generates MATCH FILES command syntax in the following format (where [name] is either a dataset or external file specification (enclosed in quotation marks)):

MATCH FILES FILE=* /FILE="[name]" /BY [key varlist]

One-to-many merge based on key values

One file contains case data, and one file is lookup table. Cases from the lookup table are merged with cases with matching key values in the case data file. The same key value can occur multiple times in the case data file. One case in the lookup table can be merged with many cases in the case data file.

• All cases in the case data file are included in the merged file.
• Cases in the table lookup file that do not have cases with matching key values in the case data file not included.
• The table lookup file cannot contain duplicate key values. If the files have multiple key variables, the key value is the combination of those values.

This setting generates MATCH FILES syntax with a TABLE subcommand for the lookup table.

Select Lookup Table

The following settings are enabled only when One-to-many merge based on key values is selected.

[active dataset name]*

The active dataset name followed by an asterisk.

• By default, this option is not selected. When selected, this setting generates syntax in the following format (where [name] is either a dataset or external file specification (enclosed in quotation marks)):

  MATCH FILES TABLE=* /FILE=”[name]” /BY [key varlist]

[second dataset name or file name]

The name of the second dataset or file.

• This is the default setting.
• When the file is external, only the file name (not the entire path) is provided (the full path is included in the generated syntax).
• When the second file is an external file (and sorting is selected, or it has string keys that require a change in the defined length), the file must first be opened and assigned a unique name.
• When the second file is an external file that needs to be opened (based on the previous condition), and the active dataset is unnamed, the active dataset must be named before the second data file is opened.
• When the second file is opened for sorting or changing string key lengths, it remains open after the merge action. Because the file was changed, you are prompted to save changes when the file is closed.
• When selected, this setting generates syntax in the following format (where [name] is either a dataset or external file specification (enclosed in quotation marks)):

  MATCH FILES FILE=* /TABLE=”[name]” /BY [key varlist]

Sort files by key values before merging

For key value merges, both files must be sorted in order of the key variable values.

• This setting is enabled only when one of the key values merge options is selected.
• If one of the files is an external file, it will be opened and sorted. The sorted file will not be saved unless you explicitly save the file.
• If the files are already sorted, you can save time by clearing this option.
• When selected, this setting generates SORT CASES syntax.

Key Variables

For key value merges, variables with the same name and basic data type (string or numeric) are included as key variables by default. Use the Variables tab to add, remove, or change the order of the key variables.

Notes:

• The selected merge always generates MATCH FILES command syntax (never STAR JOIN syntax).
• SORT CASES and ALTER TYPE command syntax takes precedence over MATCH FILES command syntax.
• DROP subcommand syntax, and optional RENAME subcommand syntax, is included based on selections that were made on the Variables tab.
• BY subcommand syntax is included if one of the key values merge options is selected.
• When string keys have different defined lengths, ALTER TYPE syntax is automatically generated to ensure equally defined lengths.

Variables tab

Use the Variables tab to add, remove, and rename variables to include in the merged file.

• Variables from the active data file are identified with an asterisk (*).
• Variables from the other data file are identified with a plus sign (+).

Excluded Variables

Variables to exclude from the new, merged data file.

• When the One-to-one merge based on file order setting is selected on the Merge Method tab, all variables in the second dataset whose names are the same as names in active dataset are excluded.
• When the One-to-one merge based on key values or One-to-many merge based on key values setting is selected on the Merge Method tab, all duplicate names variables in the second dataset (that are a different basic type (string/numeric) than the same variable names in the active dataset) are excluded.

Included Variables

Variables to include in the new, merged data file. If you want to include an excluded variable with a duplicate name in the merged data file, use Rename to change the name.

• When the One-to-one merge based on file order setting is selected on the Merge Method tab, all variables in the active dataset and all uniquely named variables in the second dataset are included.
• When the One-to-one merge based on key values or One-to-many merge based on key values setting is selected on the Merge Method tab, all uniquely named variables in both datasets are included. Variables in the active dataset, with duplicate names in second dataset that are a different basic type (string/numeric) in the second dataset, are also included.

Key Variables

For key value merges, cases are merged based on key variable values.

• The default list assignment is determined by the selected Merge Method. Variables are never automatically assigned to the Key Variables list.
  • When the One-to-one merge based on file order setting is selected on the Merge Method tab, no variables are included, the Move control is disabled, and manually dragging and dropping variables has no effect.
  • When the One-to-one merge based on key values or One-to-many merge based on key values setting is selected on the Merge Method tab, all variables with same name and same basic type (string/numeric) in both datasets are included.
• Each key variable must have the same name and same basic data type (string or numeric) in both files.
• If a key variable has different names in the two files, use Rename to change one of the names.
• If a string key variable has different defined lengths in the two files, the variable with the shorter length is automatically adjusted to the longer length. The modified version of the original file is not save unless you explicitly save it.