Mastering SPSS MATCH FILES: Merge Two Datasets the Right Way

Why Merge Matters

Researchers often collect related data in separate files—say, survey responses in one dataset and administrative records in another. To analyze them together, you must combine the files on a shared key. In SPSS, the MATCH FILES command is the tool of choice, but it has a few quirks that can silently corrupt your results if you skip the prerequisites.

Key Pre‑conditions

• Sort First: Both files must be sorted on the key variable(s) before merging. Unsatisfied sorting can drop or duplicate cases.
• Consistent Types: The key variables in each file must have the same type (numeric vs. string) and length. The TYPE= subcommand forces SPSS to treat them identically.
• Clean Keys: Trim spaces and standardize case. SPSS treats “ABC” and “abc” as different values.

Controlling the Merge: KEEP and TYPE

The KEEP= clause limits the variables that appear in the output, preventing accidental duplication of columns that share the same name. The TYPE= clause ensures that the key variables match in format. A typical merge might look like this:

GET FILE='primary.sav'.
SORT CASES BY id.
SAVE OUTFILE='primary_sorted.sav'.

GET FILE='secondary.sav'.
SORT CASES BY id.
SAVE OUTFILE='secondary_sorted.sav'.

MATCH FILES
  /FILE='primary_sorted.sav'
  /FILE='secondary_sorted.sav'
  /BY id
  /KEEP=var1 var2 var3 var4
  /TYPE=NUMERIC(id).
EXECUTE.

Replace var1–var4 with the variables you want from the secondary file. If you omit KEEP, all variables from both files will appear, which can be confusing if names collide.

Diagnosing the Merge

After running MATCH FILES, SPSS creates a MATCHED variable that flags whether each case was successfully linked. Verify the merge with these steps:

1. FREQUENCIES VARIABLES=MATCHED /FORMAT=NOTOTAL. The count of 1 should match the number of expected matches.
2. LIST VARIABLES=id var1 var2 var3 var4 /CASES=ALL. Inspect a few rows to confirm that the correct values align.
3. Compare row counts: DATASET ACTIVATE primary_sorted. DISPLAY FILES. Then DATASET ACTIVATE merged. DISPLAY FILES. The merged file should have the sum of unique IDs from both files, minus any duplicates that were dropped.

Trade‑offs and Limitations

• One‑to‑One vs. Many‑to‑Many: By default, MATCH FILES performs a one‑to‑one merge. If you have multiple rows per key in either file, you must use BY with WITH or adjust the sorting strategy to create a many‑to‑many merge.
• Memory Footprint: Large datasets can consume significant RAM during sorting and merging. Consider using SET MEMORY= or splitting the merge into smaller chunks.
• Case Sensitivity: SPSS treats string keys case‑sensitively. If your data come from heterogeneous sources, standardize case before sorting.

Actionable Checklist

• Sort both primary and secondary files on the key variable(s).
• Confirm key type and length; use TYPE= if they differ.
• Use KEEP= to avoid unwanted columns.
• Run the merge; then check MATCHED and row counts.
• If mismatches appear, investigate leading/trailing spaces or missing values in the keys.

By following these steps, you can merge datasets reliably and keep your analyses clean.