How to Merge Data Files in SPSS (Add Cases vs Add Variables)

Introduction: Why You Will Need to Merge Files in SPSS

Sooner or later, almost every SPSS user ends up with data split across two files. Maybe you collected survey responses in two waves, and each wave lives in its own file. Maybe one file holds questionnaire answers and another holds test scores, linked by a participant ID. Or maybe a colleague sent you a second dataset that belongs underneath the first one. In all of these cases, you do not want to copy and paste rows by hand. That is slow, and it is exactly how cases get duplicated, misaligned, or lost.

SPSS has two built-in tools for combining files, and choosing the wrong one is the most common mistake beginners make. Add Cases stacks one file underneath another, adding more rows. Add Variables places files side by side, adding more columns, matching rows on a key variable such as an ID. This tutorial walks through both, click by click, with the syntax you can reuse, the checks that catch mistakes, and guidance on reporting what you did.

Before you start, make a backup copy of both files. Merging creates a new working dataset, but working from copies means you can always start again if something goes wrong.

Add Cases vs Add Variables: Which One Do You Need?

Ask yourself one question: am I adding more people, or more information about the same people?

If you are adding more people — for example, wave 2 respondents underneath wave 1 respondents — you want Add Cases. The two files should have the same variables, or at least overlapping variables, and SPSS will stack the rows. Variables present in only one file will get missing values for cases from the other file.

If you are adding more information about the same people — for example, test scores to join onto questionnaire answers — you want Add Variables. The two files must share a key variable, usually an ID, and SPSS matches rows on that key. This is the option that needs the most care, because a bad match silently produces wrong data.

A quick comparison

  • Add Cases: more rows, same columns. Use when both files describe the same kind of cases.
  • Add Variables: more columns, same rows. Use when both files describe the same cases, linked by an ID.
  • Neither tool sorts or cleans your data for you. Variable names, types, and value labels should agree before you merge, or you will spend longer fixing the result than the merge itself took.

Before You Merge: The Preparation Checklist

Five minutes of preparation prevents most merge disasters. Work through this checklist for both files:

  • Check variable names. In Add Cases, SPSS matches columns by variable name. “Age” in one file and “age” in another will not match, because SPSS variable names are case-sensitive in this context. Rename first so they agree exactly.
  • Check variable types and widths. A numeric variable in one file and a string variable of the same name in the other will cause problems. Make them agree in Variable View before merging.
  • Check value labels and missing-value codes. If one file codes missing as 99 and the other leaves blanks, your merged file will mix the two. Decide on one scheme and apply it to both files first.
  • Check your key variable (Add Variables only). IDs must be unique in at least one file, and ideally in both. Duplicate IDs are the number one cause of mysterious extra rows after a merge.
  • Sort by the key variable (Add Variables only). SPSS requires both files to be sorted in ascending order of the key variable before a match merge. Sorting is a required step, not an optional tidy-up.
  • Save backup copies. Keep the originals untouched and merge copies instead.

A useful habit is to run Frequencies on the key variable in both files first. If one file has 200 unique IDs and the other has 210, you already know ten cases will not match, and you can investigate before merging rather than after.

How to Add Cases in SPSS (Stack Rows)

Suppose file A has wave 1 responses and file B has wave 2 responses, with the same questionnaire variables. Here is the click-by-click route:

  1. Open file A, the file you want to keep as the base. It does not matter much which file is the base, but starting from the larger or cleaner file is sensible.
  2. Go to Data > Merge Files > Add Cases.
  3. In the dialog, select file B. If file B is already open in another SPSS window, choose “An open dataset”; otherwise choose “An external SPSS Statistics data file” and browse to it.
  4. Click Continue. You will see two lists: “Unpaired Variables” on the left and “Variables in the New Active Dataset” on the right.
  5. Variables with identical names in both files are paired automatically and appear on the right. Variables that exist in only one file stay on the left; SPSS will include them in the merged file, with system-missing values for cases from the other file. You can move a variable from the right back to the left if you want to exclude it.
  6. Optionally, tick “Indicate case source as variable” and give it a name such as Source_File. SPSS then adds a variable coded 0 for cases from file A and 1 for cases from file B. This is highly recommended: it lets you check the merge afterwards and lets you compare waves later.
  7. Click OK. SPSS stacks the files and shows the combined dataset in Data View.

Check your work straight away. The new case count should equal the two original counts added together. Run a frequency on your source variable if you created one: it should show the two original counts. If the total is wrong, a file was filtered, or a variable mismatch pushed cases apart. Do not proceed to analysis until the counts add up.

How to Add Variables in SPSS (Match Columns by ID)

Suppose file A has questionnaire answers and file B has test scores, and both have a participant ID variable called ID. Here is the click-by-click route:

  1. Open both files. Sort each one by ID: Data > Sort Cases, move ID to the sort list, choose Ascending, and click OK. Do this for both files.
  2. Make file A the active dataset.
  3. Go to Data > Merge Files > Add Variables.
  4. Select file B (open dataset or external file) and click Continue.
  5. Tick “Match cases on key variables in sorted files”. Move ID from the list into the “Key Variables” box.
  6. Decide what to do with unmatched cases. “Both files provide cases” keeps every case from both files, with missing values where a match is missing. “Active dataset only” keeps only cases from file A. “External file only” keeps only cases from file B. For most research merges, “Both files provide cases” is the safest starting point because it hides nothing; you can filter afterwards with full information.
  7. Optionally, tick “Indicate case source as variable” and name it, for example Match_Source. This records whether each case came from one file or both, which is invaluable for checking.
  8. Review the variable lists. Variables with the same name in both files appear as excluded variables marked with an asterisk; SPSS keeps the version from the active dataset. Rename any variable you actually need from both files before merging, so nothing is silently dropped.
  9. Click OK. SPSS produces the merged dataset.

Now check the merge carefully. Cross-tabulate your source variable against itself, or simply run frequencies: the number of matched cases should equal the number of IDs present in both files. Look at a few cases by eye in Data View, especially any with missing values on the newly added variables, and confirm they are genuinely unmatched rather than wrongly matched. A quick sort by ID and a visual scan of the first twenty rows catches many errors.

What if the key variable is not unique?

If the same ID appears more than once in a file, SPSS performs what is effectively a many-to-one match, and the results can be confusing: one row in the other file can be duplicated across several rows. If your design genuinely has repeated measurements per ID, that may be what you want, but usually duplicate IDs mean a data-entry error or a file that needs restructuring first. Deduplicate or restructure before merging, and re-check frequencies afterwards.

What about many-to-many merges?

SPSS does not do a true many-to-many match merge in this dialog. If both files have duplicate IDs, stop and rethink the design: you probably need to aggregate one file first (for example, with Data > Aggregate, taking the mean score per ID) or restructure the data, rather than forcing a match that SPSS will not handle cleanly.

Paste-Ready SPSS Syntax for Merging Files

The dialogs above generate syntax, and saving that syntax is good practice: it makes your merge reproducible and lets you re-run it after fixing a data error. Click Paste instead of OK in either dialog to send the syntax to a syntax window, then save it. Here are clean examples you can adapt. Replace the file paths and variable names with your own.

For Add Cases:

ADD FILES /FILE=*
  /FILE='C:\Data\wave2.sav'
  /BY Age Gender Score.
EXECUTE.

Note: in Add Cases syntax, the /BY list is not a matching key; it simply controls variable order. The simpler and more common form just lists the files:

ADD FILES /FILE=*
  /FILE='C:\Data\wave2.sav'.
EXECUTE.

For Add Variables, matched on ID:

SORT CASES BY ID.
MATCH FILES /FILE=*
  /FILE='C:\Data\scores.sav'
  /BY ID.
EXECUTE.

If you want a source indicator variable in syntax, add it before the merge. For example, in the base file run COMPUTE Source_File = 0, and in the external file run COMPUTE Source_File = 1, before combining. In a match merge, the /BY variable is the key; make sure both files are sorted by it first, exactly as the dialog requires, or SPSS will return an error rather than a wrong result — which is the safer failure mode.

One caution: syntax uses the exact file path on your computer. If you share syntax with a colleague, they will need to change the paths. Keep the syntax file alongside the data files so the paths stay meaningful.

How to Interpret the Merged Output (and Spot a Bad Merge)

SPSS does not print a “merge report” table, so verification is your job. These checks take a few minutes and catch the errors that ruin analyses:

  • Case counts. After Add Cases, the total should equal the sum of both files. After Add Variables with “both files provide cases”, the total should equal the number of unique IDs across both files, not the sum of both files.
  • Variable counts. After Add Variables, the new columns should all be present, with sensible names and no unexpected string-to-numeric conversions.
  • Missing-value patterns. A whole block of missing values on a new variable usually means a failed match, not genuinely missing data. Investigate before you analyse.
  • Descriptive statistics. Run Descriptives on a key variable before and after merging. Means and ranges that shift dramatically suggest duplicated or dropped cases.
  • Spot checks. Pick five IDs you know well and confirm their values carried across correctly. This simple check catches column misalignment that summary statistics can miss.

If anything looks wrong, do not patch the merged file by hand. Go back to the originals, fix the underlying problem — a mismatched variable name, an unsorted file, a duplicate ID — and merge again. Hand-patching a merged file destroys reproducibility and usually introduces a second error.

Common Problems and How to Fix Them

The merge adds far more rows than expected

This usually means duplicate key values in a match merge, or that you accidentally added cases when you meant to add variables. Check ID frequencies in both files first.

Variables from the second file are all missing

This usually means the key variables do not actually match: different ID formats (for example, “001” as text in one file and 1 as a number in the other), leading spaces, or unsorted files. Standardise the ID format in both files and sort again.

A variable disappeared

In Add Variables, a variable with the same name in both files is kept only from the active dataset. Rename the second version before merging if you need both.

Value labels look wrong after merging

The merged file takes value labels from the active dataset for shared variables. If the two files labelled the same codes differently, the merged labels will be wrong for one source. Harmonise labels before merging, and use a source variable so you can check afterwards.

SPSS refuses to merge and reports the files are not sorted

Sort both files by the key variable in ascending order and try again. Note that sorting must be done on the saved files, not just in the current view, if you are merging external files.

Reporting in APA Style (7th ed.)

A merge is a data-processing decision, and like other such decisions it belongs in your Method section, usually under Participants or Data Preparation, in a sentence or two. You do not need a table for a simple merge, but you do need to say what you combined, how, and what happened to unmatched cases. Readers and reviewers need to be able to reproduce your final sample size from your description.

Example wording for an Add Cases merge: “Data from two waves of data collection (Wave 1, N = 210; Wave 2, N = 187) were combined into a single dataset using an Add Cases merge on identical questionnaire variables, yielding a final sample of N = 397. A source variable distinguished wave membership.”

Example wording for an Add Variables merge: “Questionnaire responses were matched to test scores on participant ID using a matched merge. Of 240 questionnaire respondents, 228 were successfully matched to a test record; 12 cases without a matching record were retained with missing values on the test variables and excluded pairwise from analyses using those variables.”

Report the final N used in each analysis, not just the merged N, because unmatched or missing cases can change the effective sample from one analysis to the next. If you excluded cases because they could not be matched, say so and give the numbers. Transparency here is what separates a defensible dataset from a questionable one.

Key Takeaways

  • Add Cases stacks rows (more people); Add Variables joins columns (more information about the same people, matched on an ID).
  • Before merging, harmonise variable names, types, value labels, and missing-value codes across both files.
  • For a match merge, sort both files by the key variable and check that IDs are unique and in the same format.
  • Always add a source variable so you can verify the merge and trace every case back to its origin file.
  • Verify case counts, missing-value patterns, and a few spot-checked cases before running any analysis.
  • Save the syntax: a reproducible merge is one you can re-run, explain, and defend.

Frequently Asked Questions

Can I merge more than two files at once?

Yes. In the Add Cases dialog you can add several files in one operation, and in syntax you can list multiple /FILE subcommands. For Add Variables, you can add several files in a single MATCH FILES command, provided they share the key variable. Merging two files at a time is easier to check, though, so for a first merge it is often worth doing it stepwise.

Do the variable names have to be identical for Add Cases?

Yes, for a variable to be paired across files, its name must match exactly. Variables that do not match are still included, but with missing values for cases from the other file. Renaming before merging is cleaner than tidying up afterwards.

What happens to cases that do not match in Add Variables?

It depends on the option you choose. “Both files provide cases” keeps them with missing values on the other file’s variables. The other options drop unmatched cases from one side. Choose deliberately and report the choice, because it changes your final sample size.

Can I undo a merge?

Not with an undo button after saving, which is why backups matter. If you have not saved over the originals, simply close the merged dataset without saving and start again. If you have saved, restore from your backup copies and re-run the merge.

Should I merge in SPSS or in Excel first?

For straightforward merges, SPSS is the better choice: it preserves variable types, labels, and missing-value definitions, and the syntax documents the process. Excel is tempting for quick fixes, but pasting between programs is a classic source of misaligned rows and lost formatting. Do the merge where the analysis will happen.

Need this done on your data? Get a free quote → /services/