help rangejoin

Title

rangejoin -- Form pairwise combinations if a key variable is within range

Syntax

rangejoin
keyvar low high
using
using_dataset
[, options]

by(varlist) pairwise combinations occur within groups

keepusing(varlist) variables to keep from
the using_dataset

prefix(strings:string) a stub used as a prefix when
renaming variables from the using_dataset

suffix(strings:string) a stub used as a suffix when
renaming variables from the using_dataset

all all variables from the using_dataset will be renamed

Description

This version of rangejoin requires version 1.1.0
of rangestat.
Click here to install rangestat
from SSC.

rangejoin forms pairwise combinations of observations
in memory and observations from the using_dataset when
the value of keyvar in the using_dataset
is within the range specified by low and high in the data in memory.

keyvar is a numeric variable in the using_dataset.

low or high can be specified using a numeric variable in the data
in memory.
Alternatively, low or high can be specified using
a # (a number in Stata parlance).
If a # is used and there is a numeric variable
with the same name as keyvar in the data in memory,
the bound for each observation is computed by adding # to keyvar.
If a # is used and keyvar does not exist in the data in memory,
the # is used as the bound.
Finally, you can specify low or high using a
system missing value, in which case the bound for
each observation will be missing.

rangejoin applies the same rules as inrange() for missing bounds:
if the lower bound is missing, observations will match up to and including
the value of high.
If both low and high are missing, all observations will match.
Note that the treatment of missing values for low and high
differs in this version of rangejoin and
this may require that previous code be adapted.
Without bounds, rangejoin forms the same pairwise combinations
that cross would (or joinby if the by(varlist) option is used
to restrict the matching by group).

Observations with missing values for keyvar in the using_dataset
will never be considered in range and as such will never match.
To prevent unintentional matches,
if there is a variable of the same name as keyvar
in the data in memory, no match
will occur if its value is missing for the current observation
(since low or high will most likely be computed
relative to the value of keyvar, which would lead to missing
bounds).

rangejoin will not try to find matches for
observations where low > high.

The results will include all variables from the using_dataset
unless the keepusing(varlist) is specified.
Any variable from the using_dataset will be renamed
if it also exists in the data in memory.
Use the all option if all variables from the using_dataset
are to be renamed.
When renaming variables, the
prefix(strings:string) and suffix(string) stubs are both used.
If neither
is specified, the default is to use suffix(_U).
Variables specified in by(varlist) must exist both in the data in memory
and in the using_dataset.

Example: Finding houses within the client's budget

In the following example, you have a list of houses on the market
and the current asking price.
You also have clients looking for
a house and their price range.

example do-file content

clear
input house asking
1 111
2 222
3 333
4 444
5 555
6 666
end
save "house_asking.dta"

clear
input str5 name low high
Peter 300 500
Paul 400 600
Mary 600 700
end

rangejoin asking low high using "house_asking.dta"
sort name house
list, sepby(name)

(click to run)

Do this again, this time matching
clients to houses in the same zip code.

example do-file content

clear
input house asking zip
1 111 48101
2 222 48101
3 333 48101
4 444 48101
5 555 48103
6 666 48103
end
save "house_asking.dta"

clear
input str5 name low high zip
Peter 300 500 48103
Paul 400 600 48103
Mary 600 700 48101
end

rangejoin asking low high using "house_asking.dta", by(zip)
sort name house
list, sepby(name)

(click to run)

If no house falls within the client's price range, the observation
remains but with missing values for variables from the using dataset.

Match domestic cars to similarly priced foreign cars with the same repair record

Using the iconic Stata auto dataset, we match each domestic
car to similarly priced foreign cars with the same repair record.

example do-file content

sysuse auto, clear
keep if foreign
save "foreign_cars.dta"
sort rep78 price
list make price rep78, sepby(rep78)

sysuse auto, clear
drop if foreign
rangejoin price -1000 1000 using "foreign_cars.dta", by(rep78)
gen pdiff = price - price_U
sort rep78 make price_U
list make price rep78 make_U price_U pdiff, sepby(rep78 make)

(click to run)

Note that if you do this to calculate some statistic using the joined
data and then reduce (collapse) to the original observations,
you are probably better off using rangestat directly.

Certification and efficiency considerations

rangejoin leverages the power of rangestat to quickly
determine, for each observation in memory,
the set of observations in the using_dataset
that are in range.
Once this is known, each observation in the data in memory is
expanded by the number of observations it matched in the using_dataset.
From there, the problem reduces to a m:1 merge.

You can replicate rangejoin results using cross
or joinby but this requires
forming all pairwise combinations (within groups if you are using joinby)
and then dropping observations outside the
desired interval bounds.

To demonstrate both approaches,
the following creates a dataset with a 10% ratio of cases to controls
in 5 categories.
It also saves the controls in a separate dataset.
For added flexibility, the code lets you choose the number
of observations.
If this is the first time, start with 10,000 observations.
Note that you can't go much higher if you plan to run the
example using joinby as the total number of pairwise
combinations grows exponentially.

example do-file content

clear
set seed 41234
dis "How many observations? " _request(nobs)
set obs $nobs
gen id = _n
gen case = runiform() < .1
gen category = int(runiform() * 5) + 1
gen date = mdy(1,1,2016) + runiform() * 365
format %td date
sum, format
tab case
save "project_data.dta", replace
keep if case==0
drop case
save "control_data.dta", replace

(click to run)

Now match each case with controls in the same category if the date of
the control is within +/- 1 day of the case date.

example do-file content

use if case using "project_data.dta", clear

rangejoin date -1 1 using "control_data.dta", by(category) suffix(_ctr)

drop if mi(id_ctr) // drop cases that did not find a match
sort id id_ctr
save "rangejoin_results.dta", replace

(click to run)

Repeat the same task using joinby. There's more code to write because
you must rename variables to avoid name conflicts when joining the data.

example do-file content

use "control_data.dta", clear
rename id id_ctr
rename date date_ctr
tempfile controls
save "`controls'"

use if case using "project_data.dta"

joinby category using "`controls'"
keep if inrange(date_ctr, date-1, date+1)

* confirm that we match exactly the same controls as rangejoin
sort id id_ctr
cf _all using "rangejoin_results.dta", all

(click to run)

References

Cox, N.J. 2007. Events in intervals.
Stata Journal 7: 440-443.

Cox, N.J. 2011.
Compared with ....
Stata Journal 11: 305-314.

Cox, N.J. 2014.
Self and others.
Stata Journal 14: 432-444.

Acknowledgements

Thanks to Clyde Schechter for kindly showing us an example
where rangejoin would generate an overflow when computing
interval bounds if keyvar + # could not be stored in a
variable of keyvar's data type.
This was most likely to bite when keyvar was a byte.
Observations with the overflow would be excluded from the sample.
This report led to a review of rangejoin's handling of missing
interval bounds and it was decided to follow the
same rules as inrange() and allow missing bounds.

Authors

Robert Picard

picard@netbox.com

Also see

SSC:
rangestat,
tsegen

Others:
vlookup