Hi All,

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
Here is a copy of the ECHO data set that I am using:
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
Note: I am not sure why long settle year is come as a 2 digit output because in stata it is showing as years like 1980, 1981...
Thank you.
Deb