I have information of ids from two sources: (1) a monthly purchase panel, (2) an yearly survey (with info on the exact year/month of interview). I would like to add to the monthly purchase data information on the last & next survey interview date (year/month).
Consider the toy datasets below. The first one contains info on every month individual "A" has purchased. The second one informs at which year/month individual "A" has been interviewed over the years. Note that we may have purchase data of individuals before they were first interviewed (the first five rows in the purchase data) or after they are last interviewed. We also may have cases where the earliest survey interview occurred before the first purchase or cases where the lastest interview occurred after the last purchase (the last row in the survey data).
The desired output is as below. Take the first observation in the purchase data. Because there is no previous interview date (in the survey data) relative to the purchase year/month of Jan/2008 we have missing values for the new variables `last_survey_yr` & `last_survey_mo`, whereas the variables `next_survey_yr` & `next_survey_mo` variables are filled with the next interview date of Jun/2006 (first row in the survey data).
Consider the toy datasets below. The first one contains info on every month individual "A" has purchased. The second one informs at which year/month individual "A" has been interviewed over the years. Note that we may have purchase data of individuals before they were first interviewed (the first five rows in the purchase data) or after they are last interviewed. We also may have cases where the earliest survey interview occurred before the first purchase or cases where the lastest interview occurred after the last purchase (the last row in the survey data).
Code:
clear input id year month "A" 2008 1 "A" 2008 2 "A" 2008 3 "A" 2008 4 "A" 2008 5 "A" 2008 6 "A" 2008 7 "A" 2008 8 "A" 2008 9 "A" 2008 10 "A" 2008 11 "A" 2008 12 "A" 2009 1 "A" 2009 2 "A" 2009 3 "A" 2009 4 "A" 2009 5 "A" 2009 6 "A" 2009 7 "A" 2009 8 "A" 2009 9 "A" 2009 10 "A" 2009 11 "A" 2009 12 end save purchase_panel, replace clear input str1 id survey_yr survey_mo "A" 2008 6 "A" 2009 6 "A" 2010 6 end save survey_ym, replace
Code:
clear input str1 id year month last_survey_yr last_survey_mo next_survey_yr next_survey_mo "A" 2008 1 . . 2008 6 "A" 2008 2 . . 2008 6 "A" 2008 3 . . 2008 6 "A" 2008 4 . . 2008 6 "A" 2008 5 . . 2008 6 "A" 2008 6 2008 6 2008 6 "A" 2008 7 2008 6 2009 6 "A" 2008 8 2008 6 2009 6 "A" 2008 9 2008 6 2009 6 "A" 2008 10 2008 6 2009 6 "A" 2008 11 2008 6 2009 6 "A" 2008 12 2008 6 2009 6 "A" 2009 1 2008 6 2009 6 "A" 2009 2 2008 6 2009 6 "A" 2009 3 2008 6 2009 6 "A" 2009 4 2008 6 2009 6 "A" 2009 5 2008 6 2009 6 "A" 2009 6 2009 6 2009 6 "A" 2009 7 2009 6 2010 6 "A" 2009 8 2009 6 2010 6 "A" 2009 9 2009 6 2010 6 "A" 2009 10 2009 6 2010 6 "A" 2009 11 2009 6 2010 6 "A" 2009 12 2009 6 2010 6 end
Comment