average using past group information

[Mo Gad]

Hi all,

I have company-level information as follows. For each company id in a country, I have the current location (current_loc) and new location (new_loc) that a company will move to next quarter. Now for each company that moved (e.g. id 137 moved from location 5 to location 7), I want to get two variables:
- the average of variable size using all companies located in in the same current area (e.g. for id 137 it's location 5) in the past two years
- the average of variable size using all companies located in in the new area (e.g. for id 137 it's location 7) in the past two years

* Example generated by -dataex-. To install: ssc install dataex
clear
input str1 country float company_id byte curent_loc str6 quarter byte new_loc float(size avg_size_current avg_size_new)
"X"   131 5 "2012q3" . 27.443584 . .
"X"   137 5 "2012q4" 7 23.344286 . .
"X"   140 5 "2013q1" . 16.832315 . .
"X"   219 5 "2013q2" . 11.427843 . .
"X"   165 5 "2013q3" .  53.44666 . .
"X" 14685 6 "2012q1" .  2488.442 . .
"X"   134 6 "2012q1" . 13.555255 . .
"X"   127 6 "2012q2" .   26.1684 . .
"X"    81 6 "2012q2" . 37.755157 . .
"X"    66 6 "2012q2" .  53.79955 . .
"X"     2 6 "2012q3" . 20.235474 . .
"X"  5021 6 "2012q3" .  2871.219 . .
"X"    93 6 "2012q3" .  39.22329 . .
"X"   210 6 "2012q4" 5 28.488956 . .
"X"    19 6 "2013q1" .  52.53154 . .
"X"   197 6 "2013q2" . 29.569094 . .
"X"   130 6 "2013q3" . 15.983066 . .
"X" 14427 7 "2012q2" .  2766.468 . .
"X"   146 7 "2012q2" .  44.75117 . .
"X"    92 7 "2012q2" .  44.33076 . .
"X"   164 7 "2012q3" .  56.59673 . .
"X"   158 7 "2012q3" .  32.74441 . .
"X"   186 7 "2012q3" . 13.370055 . .
"X"  1239 7 "2012q4" 5 2251.2556 . .
"X"    42 7 "2013q3" .  58.74424 . .
"X"    85 7 "2013q4" .  46.32192 . .
"X"    53 7 "2014q1" . 12.270756 . .
"X"    76 7 "2014q3" .  47.29833 . .
"X"   171 7 "2016q2" . 34.806293 . .
"X" 10144 7 "2016q3" . 2166.3647 . .
"X"    51 7 "2016q3" .   52.9871 . .
"X"    37 7 "2016q4" . 16.703777 . .
end

Thanks for your help in advance