Merge based on time-range condition

[Duong Pham]

Hello everyone,

I have 2 datasets that needed to be merged. Dataset A has unique IDs with multiple transactions for each ID. Dataset B also has unique IDs with multiple action of rating score at different time. I want to merge A and B using unique IDs. However it goes under a condition that date for each transactions must fall into the date range of rating action. For example, stock C got transactions on 01/01/2019 and 06/01/2019. It also got 2 actions of rating score on 06/01/2018 and 02/28/2019. So when merging stock C on transaction 01/01/2019 would get score that rated on 06/01/2018; transactions on 06/01/2019 would get score that rated on 02/28/2019.

Does anyone know how to approach this problem? Thank you so much!!!

[Fei Wang]

Duong, below is a simple example. Please refer to the FAQ and post your data example using command dataex so that others may conveniently operate on your datasets.

Dataset A:

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 id float trans_date
"C" 21550
"C" 21701
end
format %td trans_date

Dataset B:

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 id float(rating_date rating_score)
"C" 21336 1.5
"C" 21608 2.5
end
format %td rating_date

Code for combining A and B

use A, clear
append using B, gen(source)

gen date = cond(mi(rating_date), trans_date, rating_date)

foreach v of varlist rating_date rating_score {
    bys id (date rating_score): replace `v' = `v'[_n-1] if mi(`v')
}

keep if source == 0
drop source date

Results:

. list

     +---------------------------------------+
     | id   trans_d~e   rating~te   ratin~re |
     |---------------------------------------|
  1. |  C   01jan2019   01jun2018        1.5 |
  2. |  C   01jun2019   28feb2019        2.5 |
     +---------------------------------------+