Calculating population shares for a region, branch, firm or region and branch for a particular year in panel data

[Carl Kier]

Hello
I need to calculate population shares (of immigrants among the total population) by region, branch, firm and region*branch in a particular year. The data i have is panel data. The ways of calculating shares i could find online did not help with this, as they resulted in the population being aggregated across time for a region instead of in a single year (i tried some the suggestions here (https://www.statalist.org/forums/for...-firm-in-stata) and here (https://www.statalist.org/forums/for...gramming-stata).
dataex example (i don't think this will be that helpful but the FAQ says to include data example, i need to limit to specific variables as there are a lot of variables and the command won't run unless i specify a limited number of variables):

* Example generated by -dataex-. For more info, type help dataex
clear
input double(ielandgrp region induagg aar)
1 4  . 1980
1 4  . 1981
1 4  . 1982
1 4  . 1983
1 4  . 1984
1 4  . 1985
1 4  . 1986
1 4  . 1987
1 4  . 1988
1 3 12 1980
1 3 12 1981
1 3 12 1982
1 3 12 1983
1 3 12 1984
1 3 12 1985
1 3 12 1986
1 3 12 1987
1 3 12 1988
1 3 12 1989
1 3 12 1990
1 3  . 1991
1 3  . 1992
1 3  . 1993
1 3  . 1994
1 3  . 1995
1 3  . 1996
1 3  . 1997
1 3  . 1998
1 3  . 1999
1 3  . 2000
1 3  . 2001
2 3 11 1980
2 3 11 1981
2 3 11 1982
2 3  . 1983
2 3  . 1984
2 3  . 1985
2 3  . 1986
2 3  . 1987
2 3  . 1988
2 3  . 1989
2 3  . 1990
1 1  6 1980
1 1  6 1981
1 1  6 1982
1 1  6 1983
1 1  6 1984
1 1  6 1985
1 1  6 1986
1 1  6 1987
1 1  6 1988
1 1  6 1989
1 1  6 1990
1 1  6 1991
1 1  6 1992
1 1  6 1993
1 1  6 1994
1 1  6 1995
1 1  6 1996
1 1  6 1997
1 1  6 1998
1 1  6 1999
1 1  6 2000
1 1  6 2001
1 1  6 2002
1 1  . 2003
1 1  6 2004
1 1  . 2005
1 1  . 2006
1 1  . 2007
1 1  . 2008
1 1  . 2009
1 1  . 2010
1 1  . 1980
1 1  . 1981
1 1  . 1982
1 1  . 1983
1 1  . 1984
1 1  . 1985
1 1  . 1986
1 1  . 1987
1 1  . 1988
1 1  . 1989
1 1  . 1990
1 1  . 1991
1 1  . 1992
1 1  . 1993
1 1  . 1994
1 1  . 1995
1 1  . 1996
1 1  . 1997
1 1  . 1998
1 1  . 1999
1 1  . 2000
1 1  . 2001
1 3 11 1980
1 3 11 1981
1 3 11 1982
1 3 11 1983
1 3 11 1984
end

[Carl Kier]

I can figure out how to create a variable for the total population in a region in a specific year using the bysort and gen commands, but not how to create a variable out of one of the states in a categorical variable, which i could use to calculate share. So if i knew how to create a variable that included ie_type=2 (immigrants) and ie_type=3 (descendents of immigrants) by region and year, i should be able to calculate the share for a specific region and year. Does anyone know how to do that?

[David Benson]

Hi Carl Kier ,

And welcome to Statalist! And thanks for using dataex to share your data!
So, I think you're going to want to use Stata's egen command to create your groupings. You shared your data, but I wasn't sure what everything meant, so I created some dummy date with region, year, and immigration status. See if this helps point you in the right direction:

 dataex person_id region immigration_status year
* Example generated by -dataex-. For more info, type help dataex
clear
input byte(person_id region) str18 immigration_status int year
10 1 "child of immigrant" 2010
 6 1 "immigrant"          2010
 7 1 "immigrant"          2010
 5 1 "immigrant"          2010
 4 1 "native"             2010
 2 1 "native"             2010
 3 1 "native"             2010
 1 1 "native"             2010
 9 1 "refugee"            2010
 8 1 "refugee"            2010
21 1 "child of immigrant" 2011
23 1 "immigrant"          2011
22 1 "immigrant"          2011
24 1 "immigrant"          2011
25 1 "native"             2011
26 1 "refugee"            2011
27 1 "refugee"            2011
28 1 "refugee"            2011
12 2 "immigrant"          2010
14 2 "immigrant"          2010
15 2 "immigrant"          2010
11 2 "immigrant"          2010
13 2 "immigrant"          2010
16 2 "native"             2010
17 2 "native"             2010
19 2 "refugee"            2010
20 2 "refugee"            2010
18 2 "refugee"            2010
33 2 "child of immigrant" 2011
35 2 "child of immigrant" 2011
32 2 "immigrant"          2011
30 2 "immigrant"          2011
29 2 "immigrant"          2011
39 2 "native"             2011
37 2 "native"             2011
38 2 "native"             2011
34 2 "native"             2011
31 2 "refugee"            2011
36 2 "refugee"            2011
end


sort region year immigration_status  // technically not necessary (the egen command below will sort it, do its calculation, then sort things back)
egen region_total = count(person_id), by(region year)  // calculates total population for that region that year
egen group_total = count(person_id), by(region year immigration_status)  // same, but further broken down by immigration_status
gen group_pct = group_total / region_total  // creating the percentage of the total, for the region, year, and immigration_status

. list, noobs sepby(region year ) abbrev(14)

  +-----------------------------------------------------------------------------------------+
  | person_id   region   immigration_status   year   region_total   group_total   group_pct |
  |-----------------------------------------------------------------------------------------|
  |        10        1   child of immigrant   2010             10             1          .1 |
  |         6        1            immigrant   2010             10             3          .3 |
  |         7        1            immigrant   2010             10             3          .3 |
  |         5        1            immigrant   2010             10             3          .3 |
  |         4        1               native   2010             10             4          .4 |
  |         2        1               native   2010             10             4          .4 |
  |         3        1               native   2010             10             4          .4 |
  |         1        1               native   2010             10             4          .4 |
  |         9        1              refugee   2010             10             2          .2 |
  |         8        1              refugee   2010             10             2          .2 |
  |-----------------------------------------------------------------------------------------|
  |        21        1   child of immigrant   2011              8             1        .125 |
  |        23        1            immigrant   2011              8             3        .375 |
  |        22        1            immigrant   2011              8             3        .375 |
  |        24        1            immigrant   2011              8             3        .375 |
  |        25        1               native   2011              8             1        .125 |
  |        26        1              refugee   2011              8             3        .375 |
  |        27        1              refugee   2011              8             3        .375 |
  |        28        1              refugee   2011              8             3        .375 |
  |-----------------------------------------------------------------------------------------|
  |        12        2            immigrant   2010             10             5          .5 |
  |        14        2            immigrant   2010             10             5          .5 |
  |        15        2            immigrant   2010             10             5          .5 |
  |        11        2            immigrant   2010             10             5          .5 |
  |        13        2            immigrant   2010             10             5          .5 |
  |        16        2               native   2010             10             2          .2 |
  |        17        2               native   2010             10             2          .2 |
  |        19        2              refugee   2010             10             3          .3 |
  |        20        2              refugee   2010             10             3          .3 |
  |        18        2              refugee   2010             10             3          .3 |
  |-----------------------------------------------------------------------------------------|
  |        33        2   child of immigrant   2011             11             2    .1818182 |
  |        35        2   child of immigrant   2011             11             2    .1818182 |
  |        32        2            immigrant   2011             11             3    .2727273 |
  |        30        2            immigrant   2011             11             3    .2727273 |
  |        29        2            immigrant   2011             11             3    .2727273 |
  |        39        2               native   2011             11             4    .3636364 |
  |        37        2               native   2011             11             4    .3636364 |
  |        38        2               native   2011             11             4    .3636364 |
  |        34        2               native   2011             11             4    .3636364 |
  |        31        2              refugee   2011             11             2    .1818182 |
  |        36        2              refugee   2011             11             2    .1818182 |
  +-----------------------------------------------------------------------------------------+

. version 16: table immigration_status region year, row col

---------------------------------------------------------------
                   |              year and region              
                   | ------- 2010 ------    ------- 2011 ------
immigration_status |     1      2  Total        1      2  Total
-------------------+-------------------------------------------
child of immigrant |     1             1        1      2      3
         immigrant |     3      5      8        3      3      6
            native |     4      2      6        1      4      5
           refugee |     2      3      5        3      2      5
                   | 
             Total |    10     10     20        8     11     19
---------------------------------------------------------------

. tabulate immigration_status region if year==2011, col nofreq

                   |        region
immigration_status |         1          2 |     Total
-------------------+----------------------+----------
child of immigrant |     12.50      18.18 |     15.79 
         immigrant |     37.50      27.27 |     31.58 
            native |     12.50      36.36 |     26.32 
           refugee |     37.50      18.18 |     26.32 
-------------------+----------------------+----------
             Total |    100.00     100.00 |    100.00 

. tabulate immigration_status region if year==2011

                   |        region
immigration_status |         1          2 |     Total
-------------------+----------------------+----------
child of immigrant |         1          2 |         3 
         immigrant |         3          3 |         6 
            native |         1          4 |         5 
           refugee |         3          2 |         5 
-------------------+----------------------+----------
             Total |         8         11 |        19