Merging two data sets based on house number ranges

[Sven Schwarzer]

Hello. I want to merge the following datasets by str and hnr:

Dataset 1
str	hnr
alphastreet	2
alphastreet	7
alphastreet	12
betastreet	1
Dataset 2
str	hnr_min	hnr_max	code
alphastreet	1	8	1111
alphastreet	5	8	2222
alphastreet	6	8	2222
alphastreet	9	12	3333
betastreet	1	5	4444

As you can see, Dataset 1 has the actual hnr-value of the person. Dataset 2 has a range of hnr, that assigns to a specific code. But it is possible, that the hnr-ranges overlap so I can not cleary assign a code for every case. I dont want to merge this cases and just mark them in Dataset 1 as "no unique code". The other obs in Dataset 1 shall be merged with their clear code.

Thanks for your help!

[Andrew Musau]

See rangejoin from SSC.

clear            
input str29(str) float hnr        
"alphastreet"    2        
"alphastreet"    7        
"alphastreet"    12        
"betastreet"    1
end
tempfile dataset1
save `dataset1'
        
clear        
input str29(str) float(hnr_min hnr_max code)
"alphastreet" 1 8 1111
"alphastreet" 5    8 2222
"alphastreet" 6    8 2222
"alphastreet" 9    12 3333
"betastreet" 1 5 4444
end

*ssc install rangejoin, replace
rangejoin hnr hnr_min hnr_max using `dataset1', by(str)

Res.:

. l, sep(0)

     +----------------------------------------------+
     |         str   hnr_min   hnr_max   code   hnr |
     |----------------------------------------------|
  1. | alphastreet         1         8   1111     2 |
  2. | alphastreet         1         8   1111     7 |
  3. | alphastreet         5         8   2222     7 |
  4. | alphastreet         6         8   2222     7 |
  5. | alphastreet         9        12   3333    12 |
  6. |  betastreet         1         5   4444     1 |
     +----------------------------------------------+

[Sven Schwarzer]

Thank you so much, Andrew Musau this is exactly what I needed!