10 Common Stata Merge Errors and How to Fix Them

By Siddharth Gupta, Independent Data Consultant | LinkedIn | Updated 31 October 2026 | Code written for Stata 15 and above

Stata merge errors can quietly damage a dissertation chapter, and most students notice only when the sample size looks wrong.

I have guided students through data preparation for 12 years, and merge problems are among the most common questions they bring to me. In my experience, most Stata merge errors come from three habits: using a key that is not unique, using keys that look the same but are stored differently, and choosing a match type without checking the data. Some of these stop with an error message. The more dangerous ones run silently and hand you a wrong dataset.

What is a Stata merge? A Stata merge adds columns from a second dataset (the using file) to the dataset in memory (the master) by matching rows on one or more key variables.

What is r(459) in a merge? It is the code Stata returns when the key you named does not identify each row uniquely in the file where uniqueness is required.

This guide gives you ten Stata merge errors with code you can run, a quick table, a _merge guide, a validation do-file and a methodology-reporting template. All examples build their own small datasets, so nothing needs to be downloaded.

Stata Merge Errors at a Glance

The table maps every one of the ten Stata merge errors to its likely cause.

Stata Merge 1:1 vs 1:m vs m:1: Pick the Right Match Type

Short answer: Ask which file has a unique key. In 1:1 both files are unique, in 1:m only master is unique, and in m:1 only using is unique.

The master is the dataset in memory. The using file is the one named after using. The left side of the match type describes master, and the right side describes using.

Here is the practice data I use below. Please run it as a do-file, not line by line, so the save commands work in order.

* cd "C:/stata_practice"     // set a folder you can write to

* Master: one row per student
clear
input id str10 name
101 "Asha"
102 "Ben"
103 "Chen"
104 "Diya"
end
save students, replace

* Using: many rows per student
clear
input id subject marks
101 1 72
101 2 65
102 1 80
103 1 58
105 1 90
end
save marks, replace

Error 1: Typing 1:1 when the data is one-to-many

use students, clear
merge 1:1 id using marks

Stata stops with:

variable id does not uniquely identify observations in the using data
r(459);

This is the most common of all Stata merge errors in student do-files, because 1:1 feels like the safe default. The marks file has two rows for student 101. Run isid id on that file to confirm, then declare the real relationship.

use students, clear
merge 1:m id using marks
tab _merge

Expected result (layout may differ slightly by Stata version):

 Matching result from |
                merge |      Freq.     Percent        Cum.
-----------------------+-----------------------------------
      Master only (1) |          1       16.67       16.67
       Using only (2) |          1       16.67       33.33
          Matched (3) |          4       66.67      100.00
-----------------------+-----------------------------------
                Total |          6      100.00

Error 2: Using m:m

Of all Stata merge errors, this one worries me most, because nothing warns you. An m:m merge runs without an error message, pairs rows by position, and gives results that depend on sort order. An experienced Statalist contributor describes it as legal syntax but almost never what is wanted. You can read that explanation in this Statalist thread on merge errors with duplicate keys.

Here is what you actually wanted when both files repeat the key:

clear
input id str1 item
1 "A"
1 "B"
end
save items, replace

clear
input id str1 tag
1 "x"
1 "y"
1 "z"
end
save tags, replace

use items, clear
joinby id using tags       // 6 rows: every item with every tag

joinby forms all pairwise combinations within each key. The m:m option does not, which is why Statalist contributors describe joinby as producing what people think merge m:m should produce, all combinations. See the Statalist discussion of joinby vs 1:m merge. If you only need to add columns, you almost certainly have a 1:m or m:1 problem, not an m:m one.

Stata Merge Error r(459): What It Means and How to Fix It

Short answer: r(459) in a merge means the key does not identify each observation uniquely in the dataset where Stata requires uniqueness. The command isid reports the same problem with the same code. StataCorp describes r(459) generically as something that should be true of your data that is not, in its error code reference.

Three steps clear most Stata merge errors that end in r(459):

1. Read the last word of the message. “Master” means clean the file in memory. “Using” means clean the file on disk.
2. Run duplicates report id (name your key) and isid id on that file.
3. Decide what the repeats are: exact copies, entry errors, or real repeated measures. Then clean the data, add a second key variable, or change the match type.

Stata Merge Variables Do Not Uniquely Identify Observations: Causes and Fixes

When Stata merge variables do not uniquely identify observations, the cause is almost always in the data, not in the command. These Stata merge errors are fixed by cleaning keys, not by rewriting the merge line.

Error 3: Duplicate IDs in the master data

Say student 102 was typed twice.

use students, clear
expand 2 if id == 102           // Ben now appears twice
save students_dup, replace

use students_dup, clear
merge 1:m id using marks        // r(459): master data

duplicates report id            // checks the key only
duplicates tag id, gen(dup_flag)
list if dup_flag > 0
duplicates drop                 // only for exact copies
drop dup_flag
isid id

Use duplicates report id, not the plain version. The plain version looks for duplicates across every variable, so it can report all records as unique even when the ID repeats. This Statalist reply on the default duplicates report shows a student caught by exactly that.

If the repeated rows differ, go back to the source file. I never use duplicates drop id, force as a reflex, because it deletes whichever row Stata finds second, not the correct one. To count repeats by group, see my guide on gen vs egen in Stata.

Some students ask me whether they can simply use VLOOKUP in Excel. For a few hundred rows you can, but VLOOKUP quietly returns the first match when keys repeat. That hides exactly the duplicate problem Stata is trying to show you.

Error 4: Duplicate IDs in the using data

The same r(459) appears when you write m:1 and the using file has repeated IDs.

use marks, clear
merge m:1 id using students_dup    // r(459): using data

use students_dup, clear
isid id                            // fails here, which is the point

I tell students to run isid on the unique side before every merge. It takes five seconds.

Error 5: Incomplete key in panel data

A firm-year file merged with country data by country alone is a classic. The country file has many years per country, so country is not unique.

clear
input firm_id str3 country year sales
1 "IND" 2021 50
1 "IND" 2022 55
2 "USA" 2021 80
2 "USA" 2022 85
end
save firms, replace

clear
input str3 country year gdp_idx
"IND" 2021 100
"IND" 2022 104
"USA" 2021 100
"USA" 2022 102
end
save country_gdp, replace

use firms, clear
merge m:1 country using country_gdp         // r(459): using data
merge m:1 country year using country_gdp    // correct

Panel data nearly always needs a two-part key. When two variables are needed, the message changes to “variables country year do not uniquely identify observations”. List every key variable after the match type, then check isid country year on the using file.

Error 6: Blank or missing keys

A few visible rows can hide blank ones. In one Statalist case, a using file with only a handful of visible rows held many blank date rows, and Stata counted them as duplicates. The same thread notes that blank rows often arrive when import excel misjudges where a spreadsheet ends.

use students, clear
set obs 6                            // two blank rows creep in
merge 1:m id using marks             // r(459): master data

count if missing(id)                 // for a string key use: count if id == ""
list if missing(id)
drop if missing(id)                  // only after you know the rows are junk
merge 1:m id using marks

Find out why the blanks exist before you drop them.

When Keys Look the Same but Do Not Match

The next three Stata merge errors are sneakier. There are no duplicates, yet matches fail or go wrong.

Error 7: String key in one file, numeric in the other

Stata stops with r(106) and names the two types. StataCorp’s error code reference lists r(106) for string versus numeric mismatches (described there under append) and for strL merge keys. Key variables can be str# but cannot be strL.

use students, clear
tostring id, replace
save students_str, replace

use marks, clear
merge m:1 id using students_str       // r(106): float vs str3

use students_str, clear
destring id, replace
save students_fixed, replace
use marks, clear
merge m:1 id using students_fixed     // works

Do not use force. It makes Stata treat the mismatched values as blank, and your matches disappear silently. If the ID has leading zeros, convert numeric to text with tostring id, replace format(%05.0f).

The r(106) message can name a different shared variable than your merge key. The Statalist thread on a type-mismatch error naming the wrong variable shows this clearly. So run describe on both files and compare every shared variable, not just the key.

Error 8: Hidden spaces, case and leading zeros

“AB02” and “ab02 ” look the same to your eyes and different to Stata. Excel also turns “00123” into 123, which is a common reason keys fail to match.

clear
input str6 code score
"AB01" 10
"ab02 " 20
end
save codes_a, replace

clear
input str6 code region
"AB01" 1
"AB02" 2
end
save codes_b, replace

use codes_a, clear
merge 1:1 code using codes_b
tab _merge                            // AB01 matches; "ab02 " and AB02 do not

use codes_a, clear                    // start again from the original file
replace code = strtrim(upper(code))
merge 1:1 code using codes_b          // both rows now match

Notice the restart. After the first merge, master holds an extra unmatched “AB02” row, so cleaning the key and merging again would create a new duplicate. If your keys come from Excel or a survey platform, do this cleaning every time. My guide on data transformation in Stata covers related clean-up commands.

Error 9: Storage type and precision

Stata’s default storage type is float, and the largest integer a float stores exactly is 16,777,216. Longer IDs get rounded, so different people can end up with the same ID. StataCorp explains this in its FAQ on the accuracy of the float data type.

clear
set obs 1
gen id_float = 123456789
gen double id_double = 123456789
format id_float id_double %12.0f
list                                  // id_float shows 123456792

A type difference alone is a hint, not damage. During a merge, Stata promotes a numeric variable to a wider type to prevent truncation. The real damage happens earlier, when the ID is rounded. Store long IDs as text, and import them that way:

import delimited using "survey.csv", stringcols(1) clear
describe id

If digits are already lost, recast double will not bring them back. Go to the source file.

How to Interpret _merge in Stata

Short answer: _merge is a variable Stata creates after every merge. It tells you where each row came from: 1 for master only, 2 for using only, 3 for both.

Many Stata merge errors only show up when you read _merge.

Codes 4 and 5 can arise only when the update option is specified. The Stata manual entry for merge lists all five codes.

Error 10: Ignoring _merge

Many students run the merge and jump to regression. Do not drop _merge != 3 rows without knowing why they are unmatched. Those rows are your data quality report.

use students, clear
merge 1:m id using marks, gen(m_marks)
tab m_marks
assert inlist(m_marks, 1, 3)     // fails here: student 105 is using-only

Now the second trap. The first merge creates _merge, and a second merge cannot create it again, so Stata stops with r(110). Two tiny unique files show it:

clear
input id x
1 10
2 20
end
save file_a, replace

clear
input id y
1 5
2 6
end
save file_b, replace

use file_a, clear
merge 1:1 id using file_b
merge 1:1 id using file_b       // r(110): variable _merge already defined

The fix is drop _merge after every merge, or name each indicator yourself with gen(), as I did above with m_marks. This Statalist thread on the _merge already defined error also shows the reverse mistake: after gen(name), there is no _merge, so drop _merge returns r(111) not found.

You can also write assert() and keep() inside the merge. A caution from practice: the assert check runs before keep when both are given. A using-only row then stops the merge even though keep(match) would have removed it, as explained in this Statalist thread on assert and keep order. I prefer separate assert and keep if lines, because a supervisor reading my do-file understands them instantly.

Bonus: same-name variables are not updated unless you ask

This is a silent mistake. When both datasets hold variables with the same name, Stata keeps the master values by default.

clear
input id score
1 .
2 50
end
save score_a, replace

clear
input id score
1 70
2 90
end
save score_b, replace

use score_a, clear
merge 1:1 id using score_b
list                              // score stays missing for id 1, and 50 for id 2

Add update to fill missing master values (code 4 for id 1, and code 5 for id 2, where values conflict). Add update replace to overwrite with using values. The IPA guide suggests renaming one of the variables and keeping both is often more sensible. I agree, and I do exactly that, so nothing is overwritten.

Stata Merge Datasets with Duplicate IDs: Safe Ways to Handle Them

Duplicates are behind many Stata merge errors, but they are not always mistakes. A student with five visits legitimately repeats five times. Follow these steps:

1. Run duplicates report id on both files.
2. Tag repeats with duplicates tag id, gen(dup) and list them.
3. Decide: exact copy, entry error, or real repeated measure.
4. For exact copies, use duplicates drop. For entry errors, fix the source file.
5. For repeated measures, add the second key variable (visit, year) or choose the correct match type.
6. Confirm with isid id visit, then save the cleaned file under a new name.

When you truly need all pairings, use joinby, as shown in Error 2.

Merging Survey Files from NHANES and Understanding Society

Real survey files produce the same Stata merge errors, only bigger. NHANES releases demographics, laboratory and questionnaire data as separate files. All of them can be linked on the common participant number SEQN, though a few parts do not contain it. See the NHANES 1999 to 2000 documentation at ICPSR. Some files also hold more than one row per respondent, so test isid seqn before assuming 1:1.

Understanding Society, available through the UK Data Service, works differently. The cross-wave person identifier pidp is consistent across the files, while the household and person numbers only work within a wave. Details are in the Understanding Society guide to linking datafiles. Household identifiers are wave-specific, so there is no longitudinal household identifier, as the Understanding Society support answer on cross-wave household files explains. Merge household and individual files within a wave first, then link waves by pidp.

Three checks save time with these files:

• Stata is case-sensitive, so SEQN and seqn are different names. Check how your import named the key.
• Run isid on each person-level file before merging.
• A household file joined to a person file is usually m:1, because many people share one household.

use demographics, clear
isid seqn
merge 1:1 seqn using lab_data, gen(m_lab)
tab m_lab

Two Short Case Studies

These are composite cases built from patterns in projects I have guided. Details are changed and numbers are illustrative.

Case 1, UK public health master’s. A student merged a survey file with hospital trust codes. Only 61% of rows matched, with no error. Excel had stripped leading zeros from the codes. After tostring with format(%05.0f), matching rose to 98%, and the rest were genuine missing trusts.

Case 2, US business school project. A student needed every product paired with every regional manager for each account, and used m:m. The result had fewer rows than the all-pairs table expected, and the pairings changed when the data was re-sorted. joinby gave the full table, and the chapter’s averages changed.

Both Stata merge errors passed without any warning. Only a row-count check caught them. The second case is also a reminder to read primary vs secondary data decisions before you combine files from different sources.

What the Stata Manual, Statalist and Stata Journal Miss

Official material explains the syntax behind these Stata merge errors well. It does not always teach students how to decide. Here is where I agree and where I disagree.

The Stata Journal tip joinby is the real merge m:m is a useful correction. I agree with its message. But it assumes you already know why you chose many-to-many, and most dissertation students did not choose it, they inherited it from a forum snippet. The Stata manual entry for joinby is the better route.

Several guides are too gentle. For example, the Princeton library guide says the use of m:m merge is not usually encouraged. You can see this in the Princeton library guide to merge and append. In my view, a command that fails silently deserves a firmer rule: never use it.

Old threads also mislead. A 2006 reply explained that the old sort option implied unique, which caused r(459) in that user’s old-syntax command. That is in this 2006 Statalist reply on the sort option. Students still copy such snippets today. Check the date, and use current syntax: merge 1:m id using file.

On same-name variables, I think the IPA advice to rename is better than update or replace. I would go further: write the renaming in your do-file so a supervisor can see it. The IPA data aggregation guide is a good place to start.

Post-Merge Validation Checklist

Run these checks after every merge to catch Stata merge errors early, and write the numbers down.

1. Note the row count of both files before the merge.
2. Write down the row count you expect afterwards.
3. Run tab _merge and explain every unmatched row.
4. Confirm the new unique key with isid.
5. Check missing values in merged variables.
6. Run summary statistics and compare with the originals, as in my guide to descriptive statistics in Stata.

Copy this do-file and edit the file names and keys:

* validate_merge.do: edit the file names and keys
version 15
use students, clear
isid id                                    // key unique in master
count
display "Master rows before merge: " r(N)

merge 1:m id using marks, gen(m_marks)
tab m_marks

quietly count if m_marks == 2
local n_using_only = r(N)
display "Using-only rows: `n_using_only'"
assert `n_using_only' == 1                 // replace 1 with your expected number

isid id subject, missok                    // new key after 1:m
misstable summarize marks subject

Report the merge in your methodology chapter

Examiners want to know how your sample was built. Use this template, and see my post on how your statistical analysis plan should match your research question for how it fits your methods.

What to Learn Next

Once you can avoid Stata merge errors, these topics follow:

• Merge vs append: append stacks rows from files with the same variables. Merge adds columns.
• Reshape before merging: if one file is wide and the other is long, reshape first, otherwise the key will not line up.
• Merging many files: name each _merge variable so you keep an audit trail.

use base, clear
foreach f in wave1 wave2 wave3 {
    merge 1:1 id using `f', gen(m_`f')
}

• Frames: in Stata 16 and later, frlink and frget link datasets without physically merging them.
• AI-written merge code: if an AI tool wrote your do-file, read my guide on how to verify AI-generated dissertation analysis, because a merge can run without error and still pair the wrong rows.

When to Get Expert Help with Stata Merge Errors

If your Stata merge errors continue after these checks, pause before running any model. A wrong merge makes every later table wrong. Read how to know if you need a dissertation expert, and get in touch through Statssy when you want your do-file reviewed.

FAQ on Stata Merge Errors

Conclusion

Most Stata merge errors are not Stata’s fault. They come from keys that are not unique, keys stored differently, and results nobody checked. Fix the key, declare the right match type, read _merge, and validate.

Keep the do-file above and reuse it on every project. It stops Stata merge errors before they reach your results chapter.

About the author: Siddharth Gupta is an independent data consultant who has guided dissertation data analysis for 12 years, working in Stata, R, SPSS and Excel. He holds an MBA in Finance and a BE in Electronics and Communication Engineering, and he completed the Google Data Analytics Certificate. He writes about Stata merge errors and other data problems students face. Connect with him on LinkedIn.