I'm using the latest updated STATA 16.
Data is organised as panel data, with gvkey year strongly balanced.
An example of the data is listed below.
The problem is two fold:
For each company-year observation, I have information of their advertising spending (online, publishing and broadcast)
The issue is that when a firm buys another via M&A, it assumes the target's advertising spending as well. This would normally be all right, but the problem is that in the dataset I'm working with the advertising of the target firm is retroactively assumed in the acquirer's observations.
For example, take a look at observation number 6:
target_cusip1 == "42717P". That firm was only bought in the year 2000, but in my dataset, GVKEY == 1004 starts incurring the advertising spending of that target firm from the year 1995 (our earliest observation year).
Another issue is that the M&A data is in CUSIP6 form, therefore doesn't directly match with the GVKEY code I'm using as my main ID.
My objective is to deduct all excess over-reported spending. Meaning, in each observation where target_cusip* is not empty, I want to find how much spending went for each company prior to it being bought by the mother company, and subtract it from the mother company.
You will notice that gvkey- year is a unique identifier, i.e. isid gvkey year, however, cusip6 year is not.
I hope I've made my problem clear, and thank you for your help.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input long gvkey str6 cusip6 float year str6(target_cusip1 target_cusip2 target_cusip3 target_cusip4 target_cusip5 target_cusip6 target_cusip7 target_cusip8 target_cusip9 target_cusip10) double(spend_online spend_broadcast spend_print) 1004 "000361" 1995 "" "" "" "" "" "" "" "" "" "" 0 0 0 1004 "000361" 1996 "" "" "" "" "" "" "" "" "" "" 0 0 0 1004 "000361" 1997 "00218V" "21676E" "" "" "" "" "" "" "" "" 0 0 0 1004 "000361" 1998 "87975W" "" "" "" "" "" "" "" "" "" 0 0 .2238 1004 "000361" 1999 "" "" "" "" "" "" "" "" "" "" 0 0 .1949 1004 "000361" 2000 "42717P" "" "" "" "" "" "" "" "" "" 0 0 .0497 1004 "000361" 2001 "" "" "" "" "" "" "" "" "" "" 0 0 0 1004 "000361" 2002 "" "" "" "" "" "" "" "" "" "" 0 0 .021 1004 "000361" 2003 "" "" "" "" "" "" "" "" "" "" 0 0 0 1004 "000361" 2004 "" "" "" "" "" "" "" "" "" "" 0 0 .0016 1004 "000361" 2005 "" "" "" "" "" "" "" "" "" "" 0 0 0 1004 "000361" 2006 "" "" "" "" "" "" "" "" "" "" 0 0 0 1004 "000361" 2007 "11567P" "75810W" "86651E" "" "" "" "" "" "" "" 0 0 .011099999999999999 1004 "000361" 2008 "00320W" "" "" "" "" "" "" "" "" "" 0 0 .009699999999999999 1004 "000361" 2009 "" "" "" "" "" "" "" "" "" "" 0 0 0 1004 "000361" 2010 "06330A" "" "" "" "" "" "" "" "" "" .0497 0 0 1004 "000361" 2011 "" "" "" "" "" "" "" "" "" "" .01 0 .015 1004 "000361" 2012 "" "" "" "" "" "" "" "" "" "" .0009 0 .0275 1004 "000361" 2013 "" "" "" "" "" "" "" "" "" "" .0018 0 .5399 1004 "000361" 2014 "2C5491" "" "" "" "" "" "" "" "" "" 0 0 0 1004 "000361" 2015 "000361" "9E4796" "" "" "" "" "" "" "" "" 0 0 .09759999999999999 1004 "000361" 2016 "7E4206" "9E3023" "" "" "" "" "" "" "" "" .0009 0 .032600000000000004 1004 "000361" 2017 "" "" "" "" "" "" "" "" "" "" .0039 0 .0103 1004 "000361" 2018 "" "" "" "" "" "" "" "" "" "" .004399999999999999 .0033 .0018 1004 "000361" 2019 "" "" "" "" "" "" "" "" "" "" .0086 0 .004200000000000001 1013 "000886" 1995 "683889" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 1996 "191900" "23820X" "45069T" "69413E" "71937T" "83083Z" "" "" "" "" 0 0 0 1013 "000886" 1997 "03764V" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 1998 "09228W" "29353W" "40525J" "74178F" "" "" "" "" "" "" 0 0 0 1013 "000886" 1999 "62978P" "71703W" "84758C" "" "" "" "" "" "" "" 0 .0222 .0245 1013 "000886" 2000 "11138X" "152317" "695934" "" "" "" "" "" "" "" 0 .0585 .0803 1013 "000886" 2001 "19974H" "" "" "" "" "" "" "" "" "" 0 0 .06480000000000001 1013 "000886" 2002 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2003 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2004 "50400C" "" "" "" "" "" "" "" "" "" 0 0 1.8143 1013 "000886" 2005 "33789X" "68431N" "" "" "" "" "" "" "" "" 0 0 .133 1013 "000886" 2006 "" "" "" "" "" "" "" "" "" "" 0 0 .003 1013 "000886" 2007 "50175R" "" "" "" "" "" "" "" "" "" 0 0 .1466 1013 "000886" 2008 "" "" "" "" "" "" "" "" "" "" 0 0 .0104 1013 "000886" 2009 "" "" "" "" "" "" "" "" "" "" 0 0 .0032 1013 "000886" 2010 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2011 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2012 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2013 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2014 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2015 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2016 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2017 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2018 "" "" "" "" "" "" "" "" "" "" 0 0 0 1013 "000886" 2019 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 1995 "92791J" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 1996 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 1997 "74283T" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 1998 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 1999 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2000 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2001 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2002 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2003 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2004 "" "" "" "" "" "" "" "" "" "" 0 0 .0022 1021 "001058" 2005 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2006 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2007 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2008 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2009 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2010 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2011 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2012 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2013 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2014 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2015 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2016 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2017 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2018 "" "" "" "" "" "" "" "" "" "" 0 0 0 1021 "001058" 2019 "" "" "" "" "" "" "" "" "" "" 0 0 0 1038 "001669" 1995 "" "" "" "" "" "" "" "" "" "" 0 .047799999999999995 .0328 1038 "001669" 1996 "" "" "" "" "" "" "" "" "" "" 0 .0924 .028 1038 "001669" 1997 "" "" "" "" "" "" "" "" "" "" 0 .1337 .2743 1038 "001669" 1998 "" "" "" "" "" "" "" "" "" "" 0 .23929999999999998 .45510000000000006 1038 "001669" 1999 "" "" "" "" "" "" "" "" "" "" 0 .836 34.275999999999996 1038 "001669" 2000 "" "" "" "" "" "" "" "" "" "" 0 .7155999999999999 37.86739999999999 1038 "001669" 2001 "" "" "" "" "" "" "" "" "" "" .26589999999999997 .37140000000000006 33.7004 1038 "001669" 2002 "" "" "" "" "" "" "" "" "" "" .0612 .5394 33.8086 1038 "001669" 2003 "" "" "" "" "" "" "" "" "" "" .1754 .42990000000000006 34.2426 1038 "001669" 2004 "" "" "" "" "" "" "" "" "" "" .0317 .4207 35.249300000000005 1038 "001669" 2005 "" "" "" "" "" "" "" "" "" "" .0913 .42460000000000003 36.7898 1038 "001669" 2006 "" "" "" "" "" "" "" "" "" "" .0732 .0982 33.43429999999999 1038 "001669" 2007 "" "" "" "" "" "" "" "" "" "" .08399999999999999 .2165 36.552400000000006 1038 "001669" 2008 "" "" "" "" "" "" "" "" "" "" .0642 .21289999999999998 33.4672 1038 "001669" 2009 "" "" "" "" "" "" "" "" "" "" .5608 .5509999999999999 27.283799999999996 1038 "001669" 2010 "" "" "" "" "" "" "" "" "" "" .44050000000000006 .352 20.7027 1038 "001669" 2011 "" "" "" "" "" "" "" "" "" "" 1.1029 .23190000000000002 13.108100000000002 1038 "001669" 2012 "" "" "" "" "" "" "" "" "" "" .23580000000000004 .138 4.4379 1038 "001669" 2013 "" "" "" "" "" "" "" "" "" "" .24040000000000003 1.2590999999999999 .551 1038 "001669" 2014 "" "" "" "" "" "" "" "" "" "" .1868 .036699999999999997 .7223 1038 "001669" 2015 "" "" "" "" "" "" "" "" "" "" .1366 .1814 2.5331999999999995 1038 "001669" 2016 "" "" "" "" "" "" "" "" "" "" .5138 .3611 3.1775 1038 "001669" 2017 "" "" "" "" "" "" "" "" "" "" 1.9288 .1671 1.8609 1038 "001669" 2018 "" "" "" "" "" "" "" "" "" "" 1.7112999999999998 .057 13.2746 1038 "001669" 2019 "" "" "" "" "" "" "" "" "" "" 2.4815999999999994 .0674 4.4087 end
0 Response to Retroactively input data
Post a Comment