Indiana University
University Information Technology Services
What are archived documents?

Login is for authorized groups (e.g., UITS, OVPIT, and TCC) that need access to specialized Knowledge Base documents. Otherwise, simply use the Knowledge Base without logging in.


In Stata, how do I merge two data sets in the many-to-one relationship?

Note: The .merge command supports 1:1, 1:m, m:1, and m:m match merges. For 1:1, see In Stata, how do I merge two data sets?

Suppose you have two data sets, A.dta and B.dta (see below), which share the same key variable, id :

Data set A.dta :

+---------+ | id y | |---------| | 1 85 | | 1 47 | | 2 95 | | 4 36 | | 5 83 | +---------+

Data set B.dta :

+---------+ | id x | |---------| | 1 53 | | 2 23 | | 3 45 | +---------+

To merge these two data sets, follow the appropriate instructions below.

Stata 11 and later versions

Sort by key variable(s) first, and then enter the merge command, making sure the data set with the "many" observations is the current data set in memory (for m:1 merges). An example using the above data sets follows:

use "C:\temp\B.dta", clear sort id save "C:\temp\B.dta", replace use "C:\temp\A.dta", clear sort id merge m:1 id using "C:\temp\B.dta"

Stata 10 and earlier versions

Use the .joinby command:

use "C:\temp\A.dta", clear sort id joinby id using "C:\temp\B.dta", unmatched(both)


In both versions, the .merge command creates a variable _merge indicating the results. For example, the designation (3) in the column on the right means that the observation appears in both data sets and that they're matched:

+--------------------------------+ | id y x _merge | |--------------------------------| | 1 85 53 matched (3) | | 1 47 53 matched (3) | | 2 95 23 matched (3) | | 4 36 . master only (1) | | 5 83 . master only (1) | | 3 . 45 using only (2) | +--------------------------------+

If you have questions about using statistical and mathematical software at Indiana University, email UITS Research Analytics (formerly known as the Stat/Math Center). Research Analytics is located on the IU Bloomington campus at Woodburn Hall 200, and is open for consultation by appointment Monday-Friday 9am-5pm. For more, visit Research Analytics on the web, or call 812-855-4724 (IUB) or 317-278-4740 (IUPUI).

This is document azco in domain all.
Last modified on December 09, 2013.

I need help with a computing problem

  • Fill out this form to submit your issue to the UITS Support Center.
  • Please note that you must be affiliated with Indiana University to receive support.
  • All fields are required.

Please provide your IU email address. If you currently have a problem receiving email at your IU account, enter an alternate email address.

I have a comment for the Knowledge Base

  • Fill out this form to submit your comment to the IU Knowledge Base.
  • If you are affiliated with Indiana University and need help with a computing problem, please use the I need help with a computing problem section above, or contact your campus Support Center.