Stata Merge: Preventing Silent Data Inflation from Duplicate Keys

The Hidden Risk of Stata Merges

When combining datasets in Stata, a seemingly innocent merge can silently multiply your observations, leading to incorrect analysis. The problem isn't failed merges—it's merges that succeed too well, creating Cartesian products that inflate sample sizes without warning.

The core issue: Stata's merge command treats duplicate key values as many-to-many joins, multiplying matching rows. If your key variable isn't unique in both datasets, you get an explosion of observations that can derail regression results and statistical inference.

Understanding Join Types and Key Requirements

Stata supports four join types: one-to-one (1:1), one-to-many (1:m), many-to-one (m:1), and many-to-many (m:m). The type depends on key uniqueness in each dataset:

• One-to-one: Unique keys in both master and using datasets.
• One-to-many: Unique keys in master, duplicates in using.
• Many-to-one: Duplicates in master, unique keys in using.
• Many-to-many: Duplicates in both datasets.

Key variables must match exactly in name and data type—Stata performs no type coercion. String keys are case-sensitive and sensitive to trailing whitespace; for example, "Apple" and "apple" will not match.

The _merge Variable: Your Post-Merge Safety Net

After every merge, Stata creates a _merge variable indicating observation origin:

• 1: Master-only observation.
• 2: Using-only observation.
• 3: Matched observation.

This variable is critical for quality control. Run tabulate _merge to see match rates and identify unmatched observations that could bias your analysis.

Worked Example: Verifying Key Uniqueness Before Merge

To ensure a safe 1:1 merge, verify key uniqueness first. Assume customer_id is the key:

sysuse auto, clear
save master_cars, replace

* Load a second dataset with potential duplicates
// (Assume 'using_cars' is loaded with duplicate customer_id)
save using_cars, replace

* Check key uniqueness on the master dataset
isid make in master_cars

If isid fails on either dataset, you have duplicate keys. For a strict 1:1 merge, resolve duplicates first (e.g., keeping the first record):

bysort customer_id: keep if _n == 1
merge 1:1 customer_id using using_cars
tabulate _merge
keep if _merge == 3

Trade-offs and Limitations

Many-to-many merges are only appropriate when you explicitly require all combinations of matching records. The danger is accidental inflation from unexamined duplicates. Furthermore, conflicting non-key variables retain the master dataset's values by default; use the update option deliberately if you want using-dataset values to fill missing master values.

Actionable Checklist for Safe Merges

1. Verify key uniqueness: Run isid keyvar on each dataset before merge.
2. Clean string keys: Apply trim() and lower() consistently to prevent whitespace/case mismatches.
3. Choose join type explicitly: Match your data structure to 1:1, 1:m, m:1, or m:m.
4. Inspect _merge immediately: Use tabulate _merge to confirm match counts.
5. Filter appropriately: Use keep if _merge == 3 for matched-only analysis.

The safest approach: assume your keys have duplicates until proven otherwise. Running isid first prevents silent data inflation.