Stata `isid` Pre‑Merge Data‑Quality Gate: Verify Key Uniqueness Before Joining Datasets

When a merge inflates your results

Imagine you have a survey file that records one observation per country‑year and a master file that records firm‑level data. You intend to join them on country and year. If the same country‑year appears twice in either file, Stata’s merge will create a Cartesian product, multiplying rows and contaminating estimates.

The duplicate‑key failure mode

A many‑to‑many merge occurs when the key combination is not unique in at least one dataset. Stata does not warn you automatically; it simply expands the result set. The most common symptom is a sudden jump in observation count after the merge, which often goes unnoticed until downstream analyses are off. When rows multiply, regression coefficients become biased, standard errors shrink, and any statistical test loses validity.

Why analysts overlook duplicates

Analysts often focus on the content of the merge rather than the structure of the key, assuming the key is correct because it looks sensible. This blind spot can turn a clean‑looking join into a source of systematic error, especially when the dataset has been built from several sources.

What isid actually checks

The command isid varlist asserts that the listed variables jointly identify each observation. Internally it verifies that no two rows share the same values for all specified key variables. It works for survey, panel, transaction and master tables, and it returns a numeric return code (_rc) that you can test. The check is a compact assertion for survey, panel, transaction, and master tables whose key should be unique.

A reproducible validation gate

Run this after loading each dataset. The capture noisily wrapper suppresses harmless error messages; the if _rc branch stops execution only when the check fails. Placeholders country and year represent the key variables you intend to merge on.

capture noisily isid country year
if _rc {
    display as error "country-year is not unique; merge aborted"
    exit 1
}

To verify the gate, create a tiny dataset with two rows that share the same country year value, run the command, and confirm that the error message appears and the program exits with a non‑zero status.

Remediation, merge safeguards, and a reusable checklist

1. Define the analytical grain. Decide whether country‑year truly represents a single unit of analysis. If multiple records can legitimately share the same key (e.g., repeated surveys), you must aggregate before merging. A clear grain prevents accidental many‑to‑many joins. A well‑defined grain also makes it easier to document your analytic assumptions for collaborators.
2. Validate each dataset separately. Apply isid to every file. A failure means you must clean or aggregate that file first. The check is performed after the data are loaded but before any other processing. Document any transformations you perform so that the uniqueness check can be reproduced later.
3. Inspect duplicate records. Use duplicates summarize or summarize to see how many observations share each key. If duplicates are unexpected, examine the underlying observations to decide whether they are entry errors or legitimate multiple records.
4. Choose an explicit remediation. Options include dropping duplicates with drop if _N > 1, collapsing them with collapse (e.g., collapse mean(value)), or reshaping the data so that each key appears once.
5. Sort both sides by the key. Stata merge commands require the key variables to be ordered; run sort country year on each dataset before merging. Sorting also speeds up the merge operation. Sorting in both directions (ascending then descending) is unnecessary; a single ascending sort is sufficient.
6. Use an explicit merge type. Prefer merge m:1 or merge 1:m over the default m:1 when you know the direction of matching. This avoids accidental many‑to‑many expansions.
7. Compare row counts and unmatched‑key counts. After merging, run count if _merge == 3 to see unmatched keys and describe the new observation total. Verify that the post‑merge row count is not dramatically higher than the sum of the individual datasets. If the post‑merge row count is lower than expected, investigate possible key mismatches or dropped observations.

Trade‑off and limitation

isid guarantees uniqueness but not semantic correctness. A key can be unique yet represent the wrong grain (for example, two different firms operating in the same country‑year). Moreover, the command does not replace sorting; without a sorted key the merge will error out. The return code may vary slightly across Stata releases, so branch on _rc rather than parsing the displayed message. Finally, isid only checks the variables you name; omitting a relevant variable can hide duplicates.

Practical verification steps

First, load the dataset, then run the isid command, capture the return code, and display a friendly message. If the check passes, proceed; if not, inspect the duplicate records with duplicates summarize and decide how to handle them. When duplicates are data‑entry errors, drop them; when they represent multiple survey waves, collapse them to a single record using collapse.

Post‑merge sanity check

After merging, run tabulate _merge to see how many observations were matched, unmatched, or duplicate‑matched. Compare the pre‑merge row counts to ensure no unexpected inflation. If the counts look reasonable, you can trust the merged dataset for analysis. Following this disciplined approach not only protects your results but also builds confidence in the reproducibility of your research.