Stata merge: choosing the right merge type and avoiding duplicate-key traps

When you need to combine two Stata datasets, the useful answer up front is this: pick the merge type that matches the actual cardinality of your key variable — merge 1:1 when the key is unique in both files, merge 1:m or m:1 when it is unique on one side only — and always inspect the _merge variable afterward. Almost every bad merge comes from guessing the cardinality wrong or from unsorted, duplicated, or missing keys.

How merge actually works

The merge command joins the dataset in memory (the master) with a dataset on disk (the using) by matching on one or more key variables. Stata requires you to declare the relationship between the keys:

• 1:1 — key is unique in both datasets (one row matches one row).
• 1:m — key is unique in the master, repeated in the using (e.g., one patient, many visits).
• m:1 — key is repeated in the master, unique in the using (e.g., many individuals, one record per county).
• m:m — key repeated on both sides. Avoid this; it does not produce a proper many-to-many join and pairs rows within key groups in an arbitrary order.

Stata verifies the uniqueness you declare. If you claim 1:1 but a key appears twice, the command stops with an error like "variable id does not uniquely identify observations." That error is your friend — it catches data problems before they silently corrupt your analysis.

A worked example: individuals joined to county data

Suppose workers.dta has many rows per county and county_stats.dta has one row per county. Run these commands in the Stata Command window (no special permissions needed; you only need read access to the using file and write access to your working directory if you save results):

* Check the key is unique where it must be
use "county_stats.dta", clear
isid countyid          // errors if countyid is duplicated or missing

use "workers.dta", clear
merge m:1 countyid using "county_stats.dta", keepusing(median_income unemployment)
tab _merge

What to check after running:

• isid countyid should return silently. Any error means fix the key before merging.
• tab _merge shows the match result: 1 = master only, 2 = using only, 3 = matched. For this example you expect mostly 3s, possibly some 1s (workers in counties missing from the stats file). A large count of 2s usually means the using file has counties nobody lives in — fine — but a large count of 1s means your key coding differs between files (e.g., string vs. numeric, or padded IDs).

The keepusing() option loads only the named variables from the using file. On large files this materially reduces memory use; you can confirm with memory before and after.

Keeping or dropping unmatched rows

By default merge keeps everything. To keep only matched records plus master-only rows, use:

merge m:1 countyid using "county_stats.dta", keep(1 3) nogen

nogen suppresses the _merge variable — only use it once you have already validated a merge of the same shape, because _merge is your primary diagnostic. Note that _merge must not already exist in either dataset; drop or rename it between successive merges.

Common mistakes and their symptoms

Missing values in the key

Stata treats each missing key value as a distinct key. Rows with missing countyid will not match and will show up as _merge == 1 or 2. Check with assert !missing(countyid) before merging.

Type mismatch between files

A numeric key in one file and a string key in the other causes merge to fail outright; two string keys with different padding ("007" vs "7") fail silently by not matching. Compare with describe countyid in both files and harmonize with tostring or destring first.

Declaring 1:1 when the data are 1:m

The merge errors out rather than guessing. The fix is not to relax to m:m — it is to decide which side should be unique and declare 1:m or m:1 accordingly. If neither side is unique, you almost certainly want joinby instead, which produces a genuine pairwise (Cartesian) join within key groups.

Duplicate keys you did not know about

Diagnose with duplicates tag countyid, gen(dup) then browse if dup. Duplicates often come from an earlier collapse or append step.

Sorting assumptions

Modern Stata versions (12 and later) sort internally for merge, so an explicit sort is not required — but the older merge syntax (pre-12) did require sorted data, and old do-files may still carry that assumption. If you inherit legacy code, verify which syntax it uses.

Limits to keep in mind

• merge loads the using file's requested variables into memory; with very large files, combine keepusing() with a prior preserve/keep pass to slim the master.
• update and replace options let the using file fill in or overwrite master values, but they change the meaning of _merge codes (4 and 5 appear). Read help merge before relying on them.
• Nothing is written to disk unless you save. The merge modifies only the data in memory, so the rollback is simply not saving — but if you do save over an input file, keep a backup copy first.

Verifying the result

A minimal post-merge checklist: tab _merge for match rates, count against your expected row count (an m:1 merge should never change the master's row count), and a spot check with list countyid median_income in 1/20. If the row count grew unexpectedly, your declared cardinality was wrong — go back to duplicates tag. Type help merge in Stata for the full syntax reference for your version.