I am trying to merge two datasets. One is a firm ID -year (panel master data). The other dataset (hereafter ECHO dataset) is from the US Government's information of penalties charged to a firm due to environmental violations. So, it can happen that a firm has years when there were no violation but on the other hand, it can also happen that a firm has two or more violations in one year.
My objective is to merge some variables like "total yearly sanction" (Variable 9 below) into my master data.
I have generated some variables on the ECHO dataset that I want to merge with my panel master data. In particular, my question is to understand how I could merge the panel data with variable 9.
Code:
7) sum of fed penalty, compliance cost and SEP cost gen tot_sanction = fed_pen + sep_cost + tot_compl_amt 8) total sanction per year by f_id settle_year: gen tot_sanction_y = sum(tot_sanction) 9) variable storing total sanction per year/ by f_id settle_year: keep if _n =_N
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input int f_id str10 settle_date long settle_year double(fed_pen sep_cost tot_compl_amt) float(tot_sanction tot_sanction_y) 2 "07/28/1978" 4 . . . . 0 2 "01/29/1987" 13 8500 0 0 8500 8500 2 "04/07/1988" 14 0 0 0 0 0 2 "09/19/1989" 15 0 0 0 0 0 2 "10/18/1990" 16 112500 0 0 112500 112500 2 "09/25/1990" 16 . . . . 112500 2 "08/30/1990" 16 0 0 0 0 112500 2 "08/26/1991" 17 4920 0 0 4920 4920 2 "02/15/1991" 17 7267 0 0 7267 12187 2 "05/06/1992" 18 0 0 0 0 0 2 "09/24/1992" 18 25000 0 0 25000 25000 2 "09/24/1992" 18 25000 0 0 25000 50000 2 "06/09/1992" 18 25000 0 0 25000 75000 2 "01/29/1992" 18 0 0 0 0 75000 2 "08/07/1992" 18 9750 0 0 9750 84750 2 "12/07/1993" 19 18381 0 0 18381 18381 2 "06/30/1993" 19 0 0 0 0 18381 2 "12/07/1993" 19 18381 0 0 18381 36762 2 "06/30/1993" 19 0 0 0 0 36762 2 "12/07/1993" 19 18381 0 0 18381 55143 2 "06/30/1993" 19 0 0 0 0 55143 2 "02/25/1993" 19 . . . . 55143 2 "10/29/1993" 19 30000 0 0 30000 85143 2 "05/04/1994" 20 0 0 0 0 0 2 "10/19/1995" 21 0 0 0 0 0 2 "02/14/1995" 21 4200 0 0 4200 4200 2 "05/12/1995" 21 50000 0 0 50000 54200 2 "01/04/1995" 21 4950 0 0 4950 59150 2 "06/12/1995" 21 0 0 0 0 59150 2 "10/03/1996" 22 . . . . 0 2 "04/18/1996" 22 0 0 70000000 7.00e+07 7.00e+07 2 "10/03/1996" 22 84000 0 0 84000 70084000 2 "12/02/1997" 23 17510 0 0 17510 17510 2 "06/18/1997" 23 22500 0 0 22500 40010 2 "09/30/1997" 23 0 0 0 0 40010 2 "07/07/1997" 23 213000 0 0 213000 253010 2 "09/29/1997" 23 238000 0 0 238000 491010 2 "02/20/1997" 23 75515 0 0 75515 566525 2 "09/29/1997" 23 238000 0 0 238000 804525 2 "07/07/1997" 23 213000 0 0 213000 1017525 2 "12/01/1997" 23 0 0 0 0 1017525 2 "10/02/1997" 23 0 0 1590000 1590000 2607525 2 "05/17/1998" 24 0 0 0 0 0 2 "06/30/1998" 24 46450 0 0 46450 46450 2 "11/18/1998" 24 0 0 1000000 1000000 1046450 2 "06/30/1998" 24 46450 0 0 46450 1092900 2 "06/30/1998" 24 46450 0 0 46450 1139350 2 "07/22/1998" 24 0 0 2000000 2000000 3139350 2 "06/30/1998" 24 46450 0 0 46450 3185800 2 "02/04/1999" 25 1000 0 0 1000 1000 2 "06/14/1999" 25 0 0 0 0 1000 2 "03/24/1999" 25 0 0 0 0 1000 2 "01/15/1999" 25 143800 0 0 143800 144800 2 "09/22/2000" 26 38596 0 0 38596 38596 2 "09/22/2000" 26 7150 0 0 7150 45746 2 "09/22/2000" 26 52340 0 0 52340 98086 2 "12/20/2000" 26 5000 0 0 5000 103086 2 "04/17/2000" 26 0 0 5000000 5000000 5103086 2 "09/27/2001" 27 0 0 850000 850000 850000 2 "07/05/2002" 28 0 0 300000 300000 300000 2 "09/26/2002" 28 2810 0 0 2810 302810 2 "06/24/2002" 28 0 0 43000000 4.30e+07 43302808 2 "08/01/2002" 28 0 0 0 0 43302808 2 "09/30/2003" 29 0 0 10000 10000 10000 2 "10/06/2003" 29 0 0 800000 800000 810000 2 "11/14/2003" 29 0 0 0 0 810000 2 "09/30/2003" 29 0 0 0 0 810000 2 "02/20/2003" 29 16170 62225 5000 83395 893395 2 "08/09/2004" 30 0 0 10000 10000 10000 2 "09/30/2004" 30 0 0 7500000 7500000 7510000 2 "09/28/2004" 30 27500 165000 10000 202500 7712500 2 "05/04/2004" 30 0 0 963000 963000 8675500 2 "05/26/2004" 30 0 0 0 0 8675500 2 "06/29/2005" 31 0 0 24500000 2.45e+07 2.45e+07 2 "08/31/2005" 31 0 0 0 0 2.45e+07 2 "01/14/2005" 31 0 0 0 0 2.45e+07 2 "04/25/2005" 31 1 0 0 1 2.45e+07 2 "09/29/2005" 31 0 0 0 0 2.45e+07 2 "09/07/2006" 32 3107 11673 100 14880 14880 2 "05/01/2006" 32 1521983 0 0 1521983 1536863 2 "04/12/2007" 33 0 0 100 100 100 2 "04/12/2007" 33 0 0 100 100 200 2 "02/21/2008" 34 0 0 0 0 0 2 "08/07/2008" 34 0 0 9382412 9382412 9382412 2 "05/07/2008" 34 0 0 18000000 1.80e+07 27382412 2 "06/02/2008" 34 30000 0 0 30000 27412412 2 "09/24/2008" 34 0 0 0 0 27412412 2 "08/21/2008" 34 0 0 0 0 27412412 2 "10/27/2009" 35 0 0 27000000 2.70e+07 2.70e+07 2 "04/14/2009" 35 0 0 59000000 5.90e+07 8.60e+07 2 "12/24/2009" 35 1310 4913 100 6323 86006320 2 "09/01/2009" 35 0 0 100 100 86006424 2 "11/09/2010" 36 0 0 29980000 29980000 29980000 2 "10/07/2010" 36 0 0 5600000 5600000 35580000 2 "12/06/2011" 37 0 0 1800000 1800000 1800000 2 "02/09/2012" 38 0 0 7300000 7300000 7300000 2 "02/26/2013" 39 0 0 16390000 16390000 16390000 2 "09/25/2014" 40 65000 0 32400 97400 97400 2 "03/17/2014" 40 0 0 7830000 7830000 7927400 2 "05/05/2014" 40 0 0 2000000 2000000 9927400 2 "04/21/2014" 40 0 0 3400000 3400000 13327400 2 "11/22/2016" 42 40000 0 5500000 5540000 5540000 2 "09/26/2017" 43 0 0 7000 7000 7000 2 "01/10/2017" 43 0 0 0 0 7000 2 "04/10/2017" 43 8251 0 0 8251 15251 2 "09/29/2017" 43 50000 0 0 50000 65251 2 "09/30/2019" 45 0 0 11000000 1.10e+07 1.10e+07 2 "02/05/2020" 46 74360 0 6000 80360 80360 2 "" . . . . . 0 2 "" . . . . . 0 2 "" . 4550 0 0 4550 4550 2 "" . . . . . 4550 3 "10/09/1985" 11 4000000 0 0 4000000 4000000 3 "09/30/1990" 16 1 0 0 1 1 3 "07/16/1990" 16 . . . . 1 3 "12/20/1991" 17 0 0 0 0 0 3 "09/28/1992" 18 0 0 0 0 0 3 "08/30/1993" 19 0 0 0 0 0 3 "12/19/1994" 20 28000 0 0 28000 28000 3 "10/31/1994" 20 28000 0 0 28000 56000 3 "09/29/1995" 21 0 0 0 0 0 3 "10/02/1995" 21 182654 0 0 182654 182654 3 "06/12/1995" 21 0 0 0 0 182654 3 "04/18/1996" 22 0 0 0 0 0 3 "09/03/1997" 23 7200 0 0 7200 7200 3 "03/31/1998" 24 0 0 10000005 10000005 10000005 3 "06/08/1999" 25 0 0 0 0 0 3 "09/29/2000" 26 5000 0 0 5000 5000 3 "06/28/2002" 28 0 0 100000 100000 100000 3 "11/01/2002" 28 0 0 35000 35000 135000 3 "10/17/2002" 28 500 0 18750 19250 154250 3 "08/12/2002" 28 3500 0 0 3500 157750 3 "03/31/2003" 29 0 0 0 0 0 3 "04/10/2007" 33 0 0 0 0 0 3 "09/18/2008" 34 25033 0 0 25033 25033 3 "09/30/2009" 35 0 0 0 0 0 3 "02/09/2012" 38 0 0 7300000 7300000 7300000 3 "05/16/2014" 40 0 0 1500 1500 1500 3 "" . . . . . 0 4 "08/12/2004" 30 0 0 430000 430000 430000 4 "08/16/2004" 30 17903 144692 0 162595 592595 4 "03/30/2006" 32 57372 418300 714000 1189672 1189672 4 "09/30/2011" 37 0 0 0 0 0 4 "12/14/2011" 37 10155 125601 0 135756 135756 4 "06/03/2013" 39 0 0 1500000 1500000 1500000 4 "09/08/2014" 40 825 0 2000 2825 2825 5 "08/03/2009" 35 30000 0 0 30000 30000 5 "08/03/2009" 35 30000 0 0 30000 60000 5 "01/11/2010" 36 30000 0 110000 140000 140000 6 "09/21/2009" 35 0 0 0 0 0 7 "03/02/1994" 20 0 0 0 0 0 7 "08/25/1998" 24 400 0 0 400 400 7 "05/15/1998" 24 0 0 0 0 400 7 "06/25/1999" 25 0 0 0 0 0 7 "04/13/2000" 26 0 0 0 0 0 7 "11/02/2001" 27 0 0 0 0 0 7 "09/28/2001" 27 2640 0 0 2640 2640 7 "09/30/2003" 29 0 0 0 0 0 7 "02/28/2003" 29 5500 0 0 5500 5500 7 "10/14/2003" 29 0 0 0 0 5500 7 "09/30/2004" 30 0 0 7500000 7500000 7500000 7 "04/13/2004" 30 11880 0 0 11880 7511880 7 "01/23/2006" 32 0 0 15 15 15 7 "07/07/2006" 32 0 0 0 0 15 7 "01/23/2006" 32 0 0 15 15 30 7 "11/09/2006" 32 7000 0 0 7000 7030 7 "08/22/2006" 32 0 0 0 0 7030 7 "01/23/2006" 32 0 0 15 15 7045 7 "02/27/2007" 33 3000 0 100 3100 3100 7 "05/08/2007" 33 0 0 37000000 3.70e+07 37003100 7 "07/02/2007" 33 0 0 1400000 1400000 38403100 7 "05/07/2008" 34 0 0 18000000 1.80e+07 1.80e+07 7 "01/30/2009" 35 800 0 100 900 900 7 "05/04/2010" 36 2790 0 100 2890 2890 7 "06/27/2011" 37 0 0 0 0 0 7 "07/15/2015" 41 4080 0 0 4080 4080 7 "08/21/2015" 41 0 0 0 0 4080 7 "11/10/2016" 42 0 0 0 0 0 7 "11/10/2016" 42 0 0 0 0 0 7 "10/28/2016" 42 0 0 0 0 0 7 "06/14/2017" 43 1785 0 2500 4285 4285 7 "05/25/2017" 43 1785 0 2500 4285 8570 7 "04/12/2018" 44 800 0 0 800 800 7 "04/11/2019" 45 0 0 0 0 0 8 "09/28/2015" 41 30800 0 3000 33800 33800 10 "06/13/1985" 11 50000 0 0 50000 50000 11 "03/19/1999" 25 0 0 0 0 0 12 "07/24/2006" 32 142500 0 500000 642500 642500 14 "10/28/1991" 17 15000 0 0 15000 15000 14 "10/04/2004" 30 0 0 1000000 1000000 1000000 15 "07/25/1994" 20 . . . . 0 15 "09/25/2002" 28 0 0 0 0 0 15 "09/24/2002" 28 750 0 100 850 850 15 "08/05/2004" 30 20619 0 51513 72132 72132 15 "06/22/2010" 36 3600 0 5000 8600 8600 16 "09/24/2004" 30 0 0 0 0 0 16 "10/17/2018" 44 0 0 0 0 0 16 "01/18/2018" 44 0 0 0 0 0 20 "04/15/1992" 18 45000 0 0 45000 45000 20 "11/15/1995" 21 0 0 0 0 0 end
Thank you.
Deb
0 Response to Merge Panel Data with multiple same year dataset
Post a Comment