Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 36 ยท Data Management

Merging and Appending Datasets

Combine datasets in Stata: append stacks rows, merge matches rows by key. Learn 1:1, m:1 and 1:m merges, the _merge variable, keep() and assert() options and common failures.

Data rarely arrives in one file. Student scores are in one file and school information in another; this year's survey is separate from last year's. Stata offers two main ways to combine files: append adds more observations (stacks rows), and merge adds more variables (joins columns by a matching key). Choosing the right one and checking the result are the skills of this lesson.

flowchart LR
    subgraph A["append: more rows"]
        A1["2023 survey"] --> A3["stacked"]
        A2["2024 survey"] --> A3
    end
    subgraph M["merge: more columns"]
        M1["students<br/>(id, score)"] --> M3["joined by id"]
        M2["schools<br/>(id, school_name)"] --> M3
    end

Append stacks datasets with the same variables; merge joins datasets on a key.

append: stack datasets

STATA
use "survey_2023.dta", clear
append using "survey_2024.dta"
append using "survey_2022.dta" "survey_2021.dta"      // several at once
append using "survey_2024.dta", generate(source)      // 0 = master, 1 = first appended

Variables are matched by name. If a variable exists in only one file, the other file's observations get missing for it. If a variable is numeric in one file and a string in the other, append fails with a type mismatch. Fix the type first (destring/tostring), then append. Use generate(source) to remember which file each row came from.

Before appending, check names and types in both files with describe using "survey_2024.dta".

merge: join by a key

merge joins the dataset in memory (the master) to a file on disk (the using dataset). The kind of merge states how the key is expected to repeat:

Type Master key Using key Example
1:1 unique unique person file and person's extra file
m:1 repeats unique students (many) to school info (one per school)
1:m unique repeats school file to its students
m:m repeats repeats almost always wrong; avoid it
STATA
use "students.dta", clear
merge m:1 school_id using "schools.dta"
tabulate _merge

The type is a check: Stata verifies the key really is unique where you say it is, and stops with an error if not (for example variable id does not uniquely identify observations in the using data). That error saves you from silent wrong results.

Both datasets must have the key variable with the same name and compatible type. If they differ, rename one first. To merge on several variables use merge 1:1 id year using ....

The _merge variable

After a merge Stata creates _merge, which says where each row came from:

_merge Label Meaning
1 master only no match in using
2 using only no match in master
3 matched found in both
STATA
tabulate _merge
assert _merge == 3             // stop if anything failed to match
keep if _merge == 3            // keep only matches
drop _merge                    // you must drop it before the next merge

A second merge fails if _merge still exists. A good merge is checked: count the unmatched rows and understand them. Reasons include different id formats ("0012" vs 12), trailing spaces, different years covered, and genuinely missing records.

You can ask for the check and the filtering inside the command:

STATA
merge m:1 school_id using "schools.dta", assert(match master) keep(match) nogenerate
  • assert(match master) allows only these outcomes; anything else stops the do-file. (Allowed values: match, master, using, or numbers.)
  • keep(match) keeps only matched rows (like an inner join); keep(match master) is a left join.
  • keepusing(school_name region) brings only some variables from the using file.
  • nogenerate skips creating _merge.
  • update and replace control conflicts when both files have the same non-key variable: by default the master value wins.

Many-to-many and joinby

merge m:m does not do what people expect (it pairs rows in order and does not create all combinations). Use joinby for all pairs:

STATA
joinby customer_id using "orders.dta"      // every customer row x every order row with the same id

Typical merge failures

  1. Key type mismatch: id is a string in one file and numeric in the other. Convert before merging.
  2. Formatting: leading zeros, spaces or case differences. Use trim(), upper() and string(id, "%05.0f").
  3. Duplicate keys: isid key shows them; duplicates report key quantifies them.
  4. Wrong type: 1:1 when the data are really m:1. The error message is your friend.
  5. Overwriting _merge: forgetting drop _merge, or merging twice.
  6. Same variable names in both files: the master's values stay; the using values are ignored unless you say update. Rename on purpose before merging.

Stand-in: a merge with _merge

The Python model implements a m:1 merge by key, reports _merge codes and shows the check Stata performs on the using dataset.

Python Stand-in: merge m:1 and the _merge codes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The output shows one master-only row (_merge 1, student d), one using-only row (_merge 2, school Elm) and three matched rows (_merge 3). In a do-file you would decide what to do with each group.

Python Stand-in: append and the source marker

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You have a student file and a school file with one row per school. Which merge type fits?

    Many students share a school, so the master key repeats and the using key is unique: m:1.
  2. 2. What does _merge == 3 mean?

    3 = matched, 1 = master only, 2 = using only.
  3. 3. What does append do?

    Append adds rows; merge adds columns.
  4. 4. A second merge fails because of _merge. What should you do?

    Stata will not overwrite an existing _merge; check it, then drop it (or use nogenerate).

Summary

  • append using stacks rows and matches variables by name; types must agree.
  • merge joins on a key; the type (1:1, m:1, 1:m) is a check Stata verifies.
  • Inspect _merge (1 master only, 2 using only, 3 matched), then assert, keep or investigate.
  • Options keep(), assert(), keepusing() and nogenerate make merges safe and short.
  • Avoid m:m; use joinby for all combinations; fix key types and formats first.

Next: Reshaping Data: Wide and Long