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
Code:
* 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
0 Response to average using past group information
Post a Comment