Greetings, I have firm-level panel data for a specific country A. I want to calculate period average employment for this country A. First, I sum up employment over firms annually, and then I use a 'table' command to produce employment averages by periods (1:1998-2002; 2: 2003-2007; 3: 2010-2014; 4: 2015-2019). But for some reason when I do the check manually using annual total employment (please see the do file attached), the figures do not add up. Why? I would appreciate your advice.
Here is a code for my datafile using dataex command. I use Stata16.


Code:
* Example generated by -dataex-. To install: ssc install dataex clear input int id byte sector int(year empl) 101 1 1997 10 101 1 1998 34 101 1 1999 2 101 1 2000 3 102 1 2004 11 102 1 2005 14 102 1 2006 2 102 2 2007 5 102 2 2008 8 102 1 2009 9 102 1 2010 14 102 1 2011 23 102 1 2012 35 102 1 2013 446 102 1 2014 11 102 1 2015 12 102 1 2016 1 102 1 2017 6 102 1 2018 9 102 1 2019 10 103 1 1997 9 103 1 1998 10 103 1 1999 11 103 1 2000 12 103 1 2001 13 103 1 2002 1 103 1 2003 9 103 1 2004 10 103 1 2005 11 103 1 2006 23 103 1 2007 34 103 1 2008 45 103 1 2009 23 103 1 2010 1 103 1 2011 12 103 1 2012 13 103 1 2013 22 103 1 2014 33 103 1 2015 45 103 1 2016 67 103 1 2017 12 103 1 2018 13 103 1 2019 15 104 2 1997 13 104 2 1998 2 104 2 1999 9 104 2 2000 45 104 2 2001 89 104 2 2002 64 104 2 2003 23 104 2 2004 10 104 2 2005 1 104 2 2006 4 104 2 2007 3 104 2 2008 2 104 2 2009 89 104 2 2010 67 104 2 2011 10 105 2 2010 14 105 2 2011 15 105 2 2012 16 105 2 2013 17 105 2 2014 18 105 2 2015 19 105 2 2016 20 105 2 2017 21 105 2 2018 22 105 2 2019 23 106 3 1997 18 106 3 1998 22 106 3 1999 11 106 3 2000 12 106 3 2001 14 106 3 2002 10 106 3 2003 11 106 3 2004 14 106 3 2005 17 106 3 2006 10 106 3 2007 23 106 3 2008 56 106 3 2009 78 106 3 2010 58 106 3 2011 45 106 3 2012 47 106 3 2013 48 106 3 2014 49 106 3 2015 45 106 3 2016 48 106 3 2017 50 106 3 2018 54 106 3 2019 60 107 3 1997 10 107 3 1998 11 107 3 1999 23 107 3 2000 45 107 3 2001 90 107 3 2002 88 107 3 2003 35 107 3 2004 8 107 3 2005 10 end
and here is a do file with some outputs copied within it

Code:
xtset id year gen period=1 if year>1997 & year<2003 replace period=2 if year>2002 & year<2008 replace period=3 if year>2009 & year<2015 replace period=4 if year>2014 & year<2020 *Total employment over firms by year table year, c(sum empl) /* . table year, c(sum empl) ---------------------- year | sum(empl) ----------+----------- 1997 | 60 1998 | 79 1999 | 56 2000 | 117 2001 | 206 2002 | 163 2003 | 78 2004 | 53 2005 | 53 2006 | 60 2007 | 87 2008 | 169 2009 | 255 2010 | 198 2011 | 138 2012 | 155 2013 | 589 2014 | 198 2015 | 211 2016 | 203 2017 | 101 2018 | 132 2019 | 164 */ *aggregating employment over firms annually using egen command egen total_empl=total(empl), by(year) *averaging total annual employment over the periods 1="1998-2002"; 2="2003-2007"; 3="2010-2014"; 4="2015-2019" table period, c(mean total_empl) /* . table period, c(mean total_empl) -------------------------- period | mean(total_~l) ----------+--------------- 1 | 118.9565 2 | 65.70834 3 | 249.1111 4 | 162.2 -------------------------- */ *shouldn't the figures be as follows? /* -------------------------- period | mean(total_~l) ----------+--------------- 1 | 124.2 2 | 66.2 3 | 255.6 4 | 162.2 -------------------------- */