*! version 2.37.0 16aug2019

Title

join -- Join/merge datasets

Syntax

Many-to-one join/merge on specified key variables

join
[varlist],
from(filename)
by(varlist)
[options]

As above, but with the "using" dataset currently open instead of the "master"

join
[varlist],
into(filename)
by(varlist)
[options]

(Note: if varlist is specified, only a subset of the variables will be added)

+ from(filename [if]) filename of the using dataset, where the keys are unique

+ into(filename [if]) filename of the master dataset

* by(varlist) key variables; master_var=using_var is also allowed in case the variable names differ between datasets

uniquemaster assert that the merge will be 1:1

keep(results) specify which match results to keep

assert(results) specify required match results

generate(newvar) name of new variable to mark merge
results; default is _merge

nogenerate do not create _merge variable

nolabel do not copy value-label definitions from using

nonotes do not copy notes from using

keepnone don't add any variables from using (overrides default of _all)

verbose show internal debug info

method(string) (advanced) set method used internally for hashing

+ you must include either from() or from() but not both.

* by(varlist) is required.

Description

join is an alternative for merge,
supporting m:1 and 1:1 joins.

Advantages:

- Increasingly faster above 100,000 obs (it is O(N) instead of O(N log N))

- Does not sort the data by the keys

- Keys can have different names in master and using

Technical notes:

join works by hashing the keys, instead of sorting the data like merge does
(see
[1]
and
[2]
for a comparison of the hash+join and sort+merge algorithms).
As a result, join performs better if the datasets are not
already sorted by the by() variables, and for datasets
above 100,000 observations (due to Mata's overhead).

Options

Options

from(filename)
specifies the using filename.
This dataset is typically smaller than the master dataset
(the one currently active)
and the key variables set with by() must uniquely
identify the dataset (see isid and fisid).

If by() does not identify the using dataset
but a subset of it, you can add use {if} within from

EG: Suppose master has country-level data for a the year 2016,
and {xyz} has country-year GDP data, then you can do
using(xyz if year==2016)

into(filename) is useful if you are currently in the
using dataset. This is faster than the alternative of
saving, loading the master dataset, and then calling join
(which would then have to open the using dataset again)

by(varlist)
specifies the key variables
that will be used to join both datasets.
If the names of the variables differ, you can use
master_var=using_var (e.g. by(year=yr))

uniquemaster
ensures that the match is 1:1 instead of m:1.
Since a 1:m merge can be replicated with the into() option,
this means that all merges except m:m (not recommended),
and update/replace merges (less useful) are supported.

generate(newvar)
specifies that the variable containing match
results information should be named newvar
rather than _merge.

nogenerate specifies that _merge not be created. This
would be useful if you also specified keep(match), because
keep(match) ensures that all values of _merge would be 3.

nolabel
specifies that value-label definitions from the using file be ignored.
This option should be rare, because definitions from the master are
already used.

nonotes
specifies that notes in the using dataset not be added to the
merged dataset; see [D] notes.

keepnone
specifies that no variables from using will be added. Use this in combination
with keep(match) if you just want to keep a group of observations
from the master dataset.

noreport
specifies that join not present its summary table of
match results.

assert(results)
specifies the required match results. The possible
results are

numeric equivalent
code word (results) description

1 master observation appeared in master only
2 using observation appeared in using only
3 match observation appeared in both

Numeric codes and words are equivalent when used in the assert()
or keep() options.

The following synonyms are allowed:
masters for master,
usings for using,
matches and matched for match,
match_updates for match_update,
and
match_conflicts for match_conflict.

Using assert(match master) specifies that the merged file is
required to include only matched master or using
observations and unmatched master observations, and may not
include unmatched using observations. Specifying assert()
results in join issuing an error if there are match results
among those observations you allowed.

The order of the words or codes is not important, so all the following
assert() specifications would be the same:

assert(match master)

assert(master matches)

assert(1 3)

When the match results contain codes other than those allowed,
return code 9 is returned, and the
merged dataset with the unanticipated results is left in memory
to allow you to investigate.

keep(results)
specifies which observations are to be kept from the merged dataset.
Using keep(match master) specifies keeping only
matched observations and unmatched master observations after merging.

keep() differs from assert() because it selects
observations from the merged dataset rather than enforcing requirements.
keep()
is used to pare the merged dataset to a given set of observations when
you do not care if there are other observations in the merged dataset.
assert() is used to verify that only a given set of observations
is in the merged dataset.

You can specify both assert() and keep(). If you require
matched observations and unmatched master observations
but you want only the matched observations, then you could specify
assert(match master) keep(match).

verbose
is a programmer command that will report Mata debug information

Examples

Perform m:1 merge

webuse nlswork
replace year = 1900 + year
join xrate, by(year) from(http://www.stata-press.com/data/r14/pennxrate.dta if country=="JPN")

Author

Sergio Correia

Board of Governors of the Federal Reserve System, USA

sergio.correia@gmail.com

More Information

To report bugs, contribute, ask for help, etc. please see the project URL in Github:

https://github.com/sergiocorreia/ftools