Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating a variable that counts the number of respondents in different ethnic groups per year

    Greetings,

    I'm running Stata 15.1 and working with data from the American Community Survey. My goal is to assess differences in income across different European ethnic groups. The problem is that some of these groups have only a couple of respondents in a given year. While I ultimately plan to derive aggregate estimates (i.e. take the mean of a group between 2010-2017), precision will be naturally compromised if, say, certain groups total 10-50 respondents across all years. I'd thus like to remove groups from the analysis who have less than 20 respondents in ANY year of the survey. I first attempted the following:

    Code:
    egen grpcount=count(ancestgroup), by(year ancestgroup)
    However, when I attempted to tabulate ancestgroup while excluding respondents who had < 20 respondents in at least one year....

    Code:
    tab ancestgroup if grpcount > 19
    
                   group(ethnic1b ethnic2b) |      Freq.     Percent        Cum.
    ----------------------------------------+-----------------------------------
            British/English British/English |      2,095        0.31        0.31
                   British/English Scottish |     15,695        2.33        2.64
                      British/English Irish |     34,078        5.06        7.70
                   British/English Austrian |        266        0.04        7.73
                     British/English German |     37,342        5.54       13.27
                      British/English Dutch |      4,101        0.61       13.88
                    British/English Russian |        594        0.09       13.97
                     British/English French |      8,448        1.25       15.22
                    British/English Italian |      2,626        0.39       15.61
                      British/English Greek |         21        0.00       15.62
                    British/English Finnish |         63        0.01       15.63
                     British/English Danish |      1,624        0.24       15.87
                    British/English Swedish |      2,684        0.40       16.27
                  British/English Norweigan |      1,690        0.25       16.52
                      British/English Swiss |        648        0.10       16.61
                  British/English Hungarian |        379        0.06       16.67
                      British/English Czech |        488        0.07       16.74
                     British/English Polish |      2,025        0.30       17.04
                 British/English Lithuanian |         23        0.00       17.04
                    British/English Spanish |        397        0.06       17.10
                   British/English Canadian |         64        0.01       17.11
            British/English French Canadian |        813        0.12       17.23
                 British/English Portuguese |         86        0.01       17.25
                   Scottish British/English |     13,180        1.96       19.20
                            Scottish German |     12,306        1.83       21.03
                             Scottish Dutch |      1,559        0.23       21.26
                           Scottish Russian |         41        0.01       21.27
                            Scottish French |      2,121        0.31       21.58
                           Scottish Italian |        967        0.14       21.72
                            Scottish Danish |        271        0.04       21.76
                           Scottish Swedish |        687        0.10       21.87
                         Scottish Norweigan |        616        0.09       21.96
                         Scottish Hungarian |         20        0.00       21.96
                             Scottish Czech |         45        0.01       21.97
                            Scottish Polish |        699        0.10       22.07
                          Scottish Canadian |         40        0.01       22.08
                   Scottish French Canadian |        133        0.02       22.10
                      Irish British/English |     26,767        3.97       26.07
                             Irish Austrian |        438        0.06       26.13
                               Irish German |     50,563        7.50       33.63
                                Irish Dutch |      4,389        0.65       34.28
                              Irish Belgian |        107        0.02       34.30
                              Irish Russian |        693        0.10       34.40
                               Irish French |      7,807        1.16       35.56
                              Irish Italian |     11,743        1.74       37.30
                                Irish Greek |        312        0.05       37.35
                              Irish Finnish |        221        0.03       37.38
                               Irish Danish |        822        0.12       37.50
                              Irish Swedish |      2,803        0.42       37.92
                            Irish Norweigan |      2,046        0.30       38.22
                                Irish Swiss |        411        0.06       38.29
                            Irish Hungarian |        858        0.13       38.41
                                Irish Czech |        931        0.14       38.55
                             Irish Croatian |         41        0.01       38.56
                               Irish Polish |      5,744        0.85       39.41
                           Irish Lithuanian |        561        0.08       39.49
                             Irish Ukranian |        364        0.05       39.55
                              Irish Spanish |        538        0.08       39.63
                             Irish Canadian |        409        0.06       39.69
                      Irish French Canadian |      1,543        0.23       39.92
                               Irish Slovak |        490        0.07       39.99
                           Irish Portuguese |        549        0.08       40.07
                   Austrian British/English |        109        0.02       40.09
                             Austrian Irish |        136        0.02       40.11
                            Austrian German |        583        0.09       40.19
                           Austrian Russian |        115        0.02       40.21
                         Austrian Hungarian |        433        0.06       40.27
                            Austrian Polish |         65        0.01       40.28
                     German British/English |     36,557        5.42       45.71
                            German Scottish |     11,728        1.74       47.45
                               German Irish |     59,299        8.80       56.24
                            German Austrian |      1,325        0.20       56.44
                              German German |      3,143        0.47       56.91
                               German Dutch |     10,160        1.51       58.41
                             German Belgian |        544        0.08       58.50
                             German Russian |      3,205        0.48       58.97
                              German French |     13,605        2.02       60.99
                             German Italian |      7,897        1.17       62.16
                               German Greek |        337        0.05       62.21
                             German Finnish |        569        0.08       62.30
                              German Danish |      2,206        0.33       62.62
                             German Swedish |      6,940        1.03       63.65
                           German Norweigan |      7,652        1.14       64.79
                               German Swiss |      2,867        0.43       65.21
                           German Hungarian |      1,770        0.26       65.48
                               German Czech |      2,396        0.36       65.83
                            German Croatian |        152        0.02       65.85
                              German Polish |     10,749        1.59       67.45
                          German Lithuanian |        481        0.07       67.52
                            German Ukranian |        404        0.06       67.58
                             German Spanish |        608        0.09       67.67
                            German Canadian |        359        0.05       67.72
                     German French Canadian |      1,301        0.19       67.92
                              German Slovak |        710        0.11       68.02
                          German Portuguese |        231        0.03       68.06
                      Dutch British/English |      2,751        0.41       68.46
                             Dutch Scottish |        674        0.10       68.56
                                Dutch Irish |      4,292        0.64       69.20
                               Dutch German |      6,132        0.91       70.11
                               Dutch French |        687        0.10       70.21
                              Dutch Italian |        281        0.04       70.25
                              Dutch Swedish |        242        0.04       70.29
                            Dutch Norweigan |         22        0.00       70.29
                               Dutch Polish |        306        0.05       70.34
                             Belgian German |        329        0.05       70.39
                    Russian British/English |        556        0.08       70.47
                           Russian Scottish |         20        0.00       70.47
                              Russian Irish |        480        0.07       70.54
                           Russian Austrian |        355        0.05       70.60
                             Russian German |      1,843        0.27       70.87
                            Russian Italian |        180        0.03       70.90
                          Russian Hungarian |        362        0.05       70.95
                             Russian Polish |      2,821        0.42       71.37
                           Russian Romanian |        353        0.05       71.42
                         Russian Lithuanian |         20        0.00       71.42
                     French British/English |      6,677        0.99       72.41
                            French Scottish |      1,626        0.24       72.66
                               French Irish |      8,094        1.20       73.86
                              French German |     10,737        1.59       75.45
                               French Dutch |      1,057        0.16       75.61
                             French Italian |      2,118        0.31       75.92
                             French Swedish |        520        0.08       76.00
                           French Norweigan |        402        0.06       76.06
                              French Polish |      1,084        0.16       76.22
                             French Spanish |        473        0.07       76.29
                          French Portuguese |        107        0.02       76.30
                    Italian British/English |      5,581        0.83       77.13
                           Italian Scottish |      2,061        0.31       77.44
                              Italian Irish |     18,161        2.69       80.13
                           Italian Austrian |        331        0.05       80.18
                             Italian German |     16,237        2.41       82.59
                              Italian Dutch |        870        0.13       82.72
                            Italian Russian |        911        0.14       82.85
                             Italian French |      3,865        0.57       83.43
                            Italian Italian |         69        0.01       83.44
                              Italian Greek |        600        0.09       83.53
                            Italian Finnish |         21        0.00       83.53
                             Italian Danish |        153        0.02       83.55
                            Italian Swedish |      1,029        0.15       83.71
                          Italian Norweigan |        762        0.11       83.82
                              Italian Swiss |         40        0.01       83.82
                          Italian Hungarian |        895        0.13       83.96
                              Italian Czech |        604        0.09       84.05
                           Italian Croatian |        239        0.04       84.08
                             Italian Polish |      6,164        0.91       85.00
                         Italian Lithuanian |        431        0.06       85.06
                           Italian Ukranian |        450        0.07       85.13
                            Italian Spanish |        671        0.10       85.23
                           Italian Canadian |         42        0.01       85.23
                    Italian French Canadian |        888        0.13       85.37
                             Italian Slovak |        649        0.10       85.46
                         Italian Portuguese |        711        0.11       85.57
                      Greek British/English |        331        0.05       85.62
                                Greek Irish |        612        0.09       85.71
                               Greek German |        658        0.10       85.80
                              Greek Italian |        506        0.08       85.88
                               Greek Polish |         44        0.01       85.89
                    Finnish British/English |        315        0.05       85.93
                              Finnish Irish |        136        0.02       85.95
                             Finnish German |        603        0.09       86.04
                            Finnish Swedish |        362        0.05       86.10
                          Finnish Norweigan |         20        0.00       86.10
                     Danish British/English |      1,379        0.20       86.30
                            Danish Scottish |        175        0.03       86.33
                               Danish Irish |        681        0.10       86.43
                              Danish German |      1,676        0.25       86.68
                             Danish Swedish |        379        0.06       86.74
                           Danish Norweigan |        261        0.04       86.77
                    Swedish British/English |      2,998        0.44       87.22
                           Swedish Scottish |        911        0.14       87.35
                              Swedish Irish |      2,261        0.34       87.69
                             Swedish German |      5,505        0.82       88.51
                              Swedish Dutch |        359        0.05       88.56
                             Swedish French |        670        0.10       88.66
                            Swedish Italian |        414        0.06       88.72
                            Swedish Finnish |        276        0.04       88.76
                             Swedish Danish |        640        0.09       88.86
                          Swedish Norweigan |      2,240        0.33       89.19
                             Swedish Polish |        385        0.06       89.25
                  Norweigan British/English |      2,213        0.33       89.57
                         Norweigan Scottish |        838        0.12       89.70
                            Norweigan Irish |      1,958        0.29       89.99
                           Norweigan German |      7,104        1.05       91.04
                            Norweigan Dutch |        331        0.05       91.09
                           Norweigan French |        692        0.10       91.19
                          Norweigan Italian |        235        0.03       91.23
                           Norweigan Danish |        692        0.10       91.33
                          Norweigan Swedish |      2,927        0.43       91.77
                            Norweigan Swiss |         21        0.00       91.77
                           Norweigan Polish |        507        0.08       91.84
                  Norweigan French Canadian |         46        0.01       91.85
                      Swiss British/English |        499        0.07       91.93
                                Swiss Irish |        294        0.04       91.97
                               Swiss German |      2,313        0.34       92.31
                              Swiss Italian |        104        0.02       92.33
                  Hungarian British/English |        436        0.06       92.39
                            Hungarian Irish |        593        0.09       92.48
                           Hungarian German |      1,171        0.17       92.65
                          Hungarian Russian |        149        0.02       92.68
                          Hungarian Italian |        253        0.04       92.71
                           Hungarian Polish |        433        0.06       92.78
                      Czech British/English |        487        0.07       92.85
                                Czech Irish |        603        0.09       92.94
                               Czech German |      1,570        0.23       93.17
                              Czech Italian |         26        0.00       93.18
                               Czech Polish |        308        0.05       93.22
                            Croatian German |        135        0.02       93.24
                     Polish British/English |      2,719        0.40       93.65
                            Polish Scottish |        792        0.12       93.76
                               Polish Irish |      5,671        0.84       94.60
                            Polish Austrian |        317        0.05       94.65
                              Polish German |     10,750        1.59       96.25
                               Polish Dutch |        497        0.07       96.32
                             Polish Russian |      2,652        0.39       96.71
                              Polish French |      1,561        0.23       96.95
                             Polish Italian |      2,957        0.44       97.38
                             Polish Swedish |        538        0.08       97.46
                           Polish Norweigan |        445        0.07       97.53
                           Polish Hungarian |        705        0.10       97.63
                               Polish Czech |        802        0.12       97.75
                            Polish Croatian |         23        0.00       97.76
                          Polish Lithuanian |        750        0.11       97.87
                            Polish Ukranian |        835        0.12       97.99
                     Polish French Canadian |        371        0.06       98.05
                              Polish Slovak |        646        0.10       98.14
                 Lithuanian British/English |         23        0.00       98.15
                           Lithuanian Irish |        286        0.04       98.19
                          Lithuanian German |        400        0.06       98.25
                          Lithuanian Polish |        234        0.03       98.28
                   Ukranian British/English |         21        0.00       98.29
                             Ukranian Irish |         87        0.01       98.30
                            Ukranian German |        375        0.06       98.35
                            Ukranian Polish |        242        0.04       98.39
                    Spanish British/English |        372        0.06       98.45
                              Spanish Irish |        609        0.09       98.54
                             Spanish German |        738        0.11       98.65
                             Spanish French |        428        0.06       98.71
                            Spanish Italian |        496        0.07       98.78
                   Canadian British/English |         43        0.01       98.79
                             Canadian Irish |         24        0.00       98.79
                            Canadian German |         97        0.01       98.81
            French Canadian British/English |      1,143        0.17       98.98
                   French Canadian Scottish |        355        0.05       99.03
                      French Canadian Irish |      1,606        0.24       99.27
                     French Canadian German |      1,424        0.21       99.48
                    French Canadian Italian |        350        0.05       99.53
                     French Canadian Polish |        366        0.05       99.58
                               Slovak Irish |        275        0.04       99.63
                              Slovak German |        492        0.07       99.70
                             Slovak Italian |         41        0.01       99.70
                              Slovak Polish |        262        0.04       99.74
                 Portuguese British/English |        272        0.04       99.78
                           Portuguese Irish |        562        0.08       99.87
                          Portuguese German |        313        0.05       99.91
                          Portuguese French |        267        0.04       99.95
                         Portuguese Italian |        314        0.05      100.00
    ----------------------------------------+-----------------------------------
                                      Total |    674,040      100.00
    ....I notice that groups are listed that shouldn't be (e.g. Slovak-Italian, N=41).

    I suspect there's a simple solution to this, but I wasn't sure. Any help you can provide is much appreciated.

    -Zach

  • #2
    ....I notice that groups are listed that shouldn't be (e.g. Slovak-Italian, N=41).
    Your -egen- command creates grpcount to be the number of observations for a given ethnic group in a given year. If Slovak_Italian has more than 10 observations in some year, any year, it will satisfy the -if- condition on your -tab- command, and it will therefore appear in the table, even though there may be other years where it has fewer than 20 observations.

    If you want to drop those groups that have fewer than 20 observations in any year:

    Code:
    by ancestgroup year, sort: gen how_many_this_year = _N
    by ancestgroup (how_many_this_year), sort: drop if how_many_this_year[1] < 20

    Comment


    • #3
      Hey Clyde,

      Thanks for this. Wouldn't 'drop' here remove them entirely from the dataset? Is there a way to just remove them from the variable (or analysis)?

      Comment


      • #4
        You did say that you wanted to "remove" those groups, which to me would mean you want to -drop- them.

        If you prefer you can replace the values of selected variables with missing values, which, in turn, would exclude them from most analyses.

        Code:
        by ancestgroup year, sort: gen how_many_this_year = _N
        foreach v of varlist this_one that_one the_other_one {
            by ancestgroup (how_many_this_year), sort: replace `v' = . if how_many_this_year[1] < 20
        }
        Note: This assumes that the variables you are replacing with missing are numeric.
        Last edited by Clyde Schechter; Yesterday, 23:13.

        Comment

        Working...
        X