Merging based on a range of dates

[Anoush Khachatryan]

Hello,

I have the following data below:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(start end group) str25 state double(occupation transportation)
120 165 1 "CA" 1 0
165 170 1 "CA" 1 0
200 236 2 "NH" 3 1
236 242 2 "NH" 3 1
end
format %tq start
format %tq end

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(employment_date) str25 state double(occupation transportation)
126 "CA" 1 0
231 "NH" 3 1
240 "NH" 3 1
212 “CT” 2 0
end
format %tq employment_date

I want to merge the two data sets based on whether the employment_date variable falls between the start and end dates. The state, occupation, and transportation variables must match as well. I would appreciate any assistance.

Thanks,
Anoush K.

[Clyde Schechter]

This cannot be done with -merge-. It is best done using Robert Picard's -rangejoin- command, available from SSC.

use first_data_set, clear
rangejoin employment_date start end using second_data_set, by(state occupation transportation)

To use -rangejoin- you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC.

[Anoush Khachatryan]

Clyde Schechter Thank you! You are always very helpful.