I've tried finding an answer for this question for a while.
I'm using the latest STATA 16.
Data is organized as panel, where gvkey-year is unique ID.
. xtset gvkey year
panel variable: gvkey (strongly balanced)
time variable: year, 1995 to 2019
delta: 1 year
Some firm-year observations have a populated gvkey-daughter(s). It means that in that year, the daughter(s) was acquired.
My task is to go for each year prior to and including that year, and deduct the daughter's spend_online spend_print spend_broadcast spend_total from the mother firm.
For example, row 22 states that firm 1078 bought firm 25279 in 2006.
I need to deduct all the spending which happened for daughter firm 25279 from the mother firm 1078 each year up to and including 2006.
Here's an example of the dataset:
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input long gvkey float year double(spend_online spend_print spend_broadcast spend_total) long(gvkey_daughter1 gvkey_daughter2) 1076 2010 .0257 .33049999999999996 13.434700000000001 13.790899276733398 . . 1076 2011 .3282 .5553 18.15500000000001 19.03849983215332 . . 1076 2012 .26589999999999997 .8386 30.700499999999998 31.80500030517578 . . 1076 2013 .3706 .4599 30.974000000000007 31.804500579833984 . . 1076 2014 2.4829000000000003 .3482 28.365099999999998 31.19620132446289 . . 1076 2015 .9530999999999998 .1413 17.1545 18.248899459838867 . . 1076 2016 2.9828999999999994 .0208 15.937800000000003 18.941499710083008 . . 1076 2017 3.4616000000000002 .024399999999999998 13.327200000000001 16.813199996948242 . . 1076 2018 1.5602 .0383 20.083099999999998 21.681598663330078 . . 1076 2019 5.543699999999999 .0139 20.8808 26.438400268554688 . . 1078 1995 0 12.4567 47.3091 59.76580047607422 25937 . 1078 1996 0 16.9126 56.3847 73.29730224609375 30418 . 1078 1997 0 17.1808 46.56040000000001 63.7411994934082 30281 . 1078 1998 0 7.433999999999999 55.819 63.25299835205078 . . 1078 1999 0 21.723400000000005 79.42639999999999 101.14979553222656 61500 . 1078 2000 0 26.034599999999998 111.06299999999999 137.0976104736328 . . 1078 2001 .0726 13.784099999999997 96.32830000000001 110.18500518798828 62784 . 1078 2002 .0023 25.5698 76.0206 101.59269714355469 . . 1078 2003 .0107 48.2835 92.10079999999998 140.39498901367188 . . 1078 2004 1.4334 51.208299999999994 80.6884 133.33010864257813 . . 1078 2005 2.8588999999999998 42.643 76.849 122.35089874267578 . . 1078 2006 3.0080999999999993 53.27559999999998 62.5927 118.87639617919922 25279 64542 1078 2007 5.268600000000001 42.4901 40.3868 88.14550018310547 . . 1078 2008 2.1693 51.03800000000001 98.93910000000001 152.14639282226563 . . 1078 2009 3.7449 36.9068 78.59470000000002 119.24640655517578 148870 125794 1078 2010 4.483199999999998 24.746599999999997 78.2569 107.4866943359375 . . 1078 2011 7.376499999999999 27.428100000000008 71.30530000000002 106.10990142822266 . . 1078 2012 11.5912 26.6229 92.0606 130.2747039794922 . . 1078 2013 5.6267000000000005 27.422000000000004 151.44709999999995 184.49578857421875 . . 1078 2014 15.162900000000004 17.7038 94.52859999999998 127.39530181884766 . . 1078 2015 11.3485 30.5301 94.48270000000001 136.36129760742188 . . 1078 2016 6.527200000000001 18.566800000000004 74.9046 99.99860382080078 146156 9372 1078 2017 5.643000000000001 7.431600000000001 61.06630000000001 74.14089965820313 . . 1097 2009 0 0 0 0 . . 1097 2010 0 0 0 0 . . 1097 2011 0 0 0 0 . . 1097 2012 0 0 0 0 . . 1097 2013 0 0 0 0 . . 1097 2014 0 0 0 0 . . 1097 2015 0 0 0 0 . . 1097 2016 0 0 0 0 . . 1097 2017 0 0 0 0 . . 1097 2018 0 0 0 0 . . 1097 2019 0 0 0 0 . . 1099 1995 0 0 0 0 . . 1099 1996 0 0 0 0 . . 1099 1997 0 0 0 0 . . 1099 1998 0 0 0 0 . . 1099 1999 0 .0014 0 .00139999995008111 . . 1099 2000 0 0 0 0 . . 1099 2001 0 0 0 0 . . 1099 2002 0 0 0 0 . . 1099 2003 0 0 0 0 . . 1099 2004 0 .0275 0 .027499999850988388 . . 1099 2005 0 0 0 0 . . 1099 2006 0 0 0 0 . . 1099 2007 0 0 0 0 . . 1099 2008 0 .0027 0 .0027000000700354576 . . 1099 2009 0 0 0 0 . . 1099 2010 0 0 0 0 . . 1099 2011 0 0 0 0 . . 1099 2012 0 0 0 0 . . 1099 2013 0 0 0 0 . . 1099 2014 0 0 0 0 . . 1099 2015 0 0 0 0 . . 1099 2016 0 0 0 0 . . 1099 2017 0 0 0 0 . . 1099 2018 0 0 0 0 . . 1099 2019 0 0 0 0 . . 1104 1995 0 0 0 0 . . 1104 1996 0 0 0 0 . . 1104 1997 0 0 0 0 . . 1104 1998 0 0 0 0 . . 1104 1999 0 .0001 0 .00009999999747378752 . . 1104 2000 0 0 0 0 . . 1104 2001 0 0 0 0 . . 1104 2002 0 0 0 0 . . 1104 2003 0 0 0 0 . . 1104 2004 0 0 0 0 . . 1104 2005 0 .003 0 .003000000026077032 . . 1104 2006 0 0 0 0 . . 1104 2007 0 .0135 0 .013500000350177288 . . 1104 2008 0 .0399 0 .039900001138448715 . . 1104 2009 0 0 0 0 . . 1104 2010 0 0 0 0 . . 1104 2011 0 0 0 0 . . 1104 2012 0 0 0 0 . . 1104 2013 .0024000000000000002 0 0 .002400000113993883 . . 1104 2014 .0823 0 0 .08229999989271164 . . 1104 2015 .2411 .21830000000000002 0 .4593999981880188 . . 1104 2016 .0096 .31989999999999996 0 .3294999897480011 . . 1104 2017 .2336 .26089999999999997 0 .4945000112056732 . . 1104 2018 1.2824 0 0 1.2824000120162964 . . 1104 2019 .0895 0 0 .08950000256299973 . . 1111 1995 0 .802 .42469999999999997 1.226699948310852 . . 1111 1996 0 .9505 0 .9505000114440918 . . 1111 1997 0 .0405 2.0658000000000003 2.106299877166748 . . 1111 1998 0 .1466 4.8281 4.974699974060059 . . 1111 1999 0 2.4535999999999993 7.326100000000001 9.77970027923584 31704 . 1228 2002 0 0 0 0 . . end format %ty year
0 Response to Deducting values in panel data across observations
Post a Comment