merge: join datasets and inspect _merge
Join two datasets by a shared identifier, then use the generated match status to find records that did not pair as expected.
Independent software. Not affiliated with, sponsored by, or endorsed by StataCorp LLC.
Datasets and complete example
The master dataset contains scores for 24 synthetic students. The using dataset contains school and program values for 23 identifiers. Two master records have no school match, and one school record has no score match.
clear
import delimited "data/student_scores.csv"
merge 1:1 id using "data/student_schools.csv"
tabulate _merge
list id math school _merge if _merge != 31:1 id declares that id should identify at most one row in each dataset. The two files stay separate in the repository so the example exercises a real join.
Output from stats.camp
merge result: matched=22, master only=2, using only=1
_merge | Freq. Percent Cum.
------------+-----------------------------------
1 | 2 8.00 8.00
2 | 1 4.00 12.00
3 | 22 88.00 100.00
------------+-----------------------------------
Total | 25 100.00
+-------------------------------------+
| # id math school _merge |
|-------------------------------------|
| 23 23 63 1 |
| 24 24 69 1 |
| 25 25 West 2 |
+-------------------------------------+
How to read _merge
_merge == 1 means the row appeared only in the master score data, _merge == 2 means it appeared only in the using school data, and _merge == 3 means the identifier matched. Here, IDs 23 and 24 lack school records, ID 25 lacks a score record, and 22 identifiers matched.
Do not discard _merge until you understand every unmatched row. An unexpected match rate often reveals duplicate keys, inconsistent identifiers, or incomplete source data.
Checks, variants, and limits
- Confirm the key is unique on the side where the merge type requires uniqueness; duplicate identifiers can change the join or produce an error.
- Choose
1:1,1:m, orm:1from the actual relationship between rows, not from the result you hope to obtain. - Inspect variables with the same name in both files so you know which values should be retained.
- Review unmatched rows before applying
keep(), dropping_merge, or saving over a dataset.
stats.camp supports common merge forms and selected options, but does not claim every Stata behavior or option interaction. See the current merge entry and Stata's merge manual for an external reference.
Related guides
Run this exact example
Clone the example repository in stats.camp and open the exact do-file shown on this page. No stats.camp account is required.
Open this example