Aggregating Data/ Loop

[Ellie Daglish]

Hello All,

I have a longitudinal panel data set with 633 constituency codes (_pcon) ranging from 0 to 632. There are 8 'waves' (ranging 1-8) for each constituency, each representing a different time that the data was collected. I have a binary indicator if an individual at each occasion is unemployed or not. There are 278,417 observations in total, each with a corresponding _pcon and (multiple) wave. I would like to aggregate my unemployment data to calculate the mean unemployment rate for each constituency in each wave, i.e. a percentage of unemployment for each constituency 0 to 632 and each wave 1 to 8 in my sample, resulting in 5064 mean values.


So far I been doing this:

. summ unemployed if _pcon==0 & wave ==1

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
  unemployed |         80       .0875    .2843491          0          1


. summ unemployed if _pcon==0 & wave ==2

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
  unemployed |         66    .0909091    .2896827          0          1


. summ unemployed if _pcon==0 & wave ==3

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
  unemployed |         61    .0983607    .3002731          0          1


. summ unemployed if _pcon==0 & wave ==4

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
  unemployed |         54    .0555556    .2312123          0          1


. summ unemployed if _pcon==0 & wave ==5

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
  unemployed |         58    .0172414    .1313064          0          1


. summ unemployed if _pcon==0 & wave ==6

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
  unemployed |         86    .1046512    .3078988          0          1


. summ unemployed if _pcon==0 & wave ==7

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
  unemployed |         75    .0933333    .2928579          0          1


. summ unemployed if _pcon==0 & wave ==8

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
  unemployed |         74    .1756757    .3831416          0          1

I have then been manually generating the unemployment percent via the Mean*100. I realise that this method is extremely inefficient. I have been trying to research a better way of doing this however am new to STATA and have been unsuccessful.

Moreover, to merge with my existing data file in another software, if possible I would require a mean unemployment for each of my 228, 417 observations so that I can copy and paste the mean aggregate unemployment for each observation.

I was hoping there was some way (maybe a loop) of doing it in Stata?

I hope that I have made myself clear. Many thanks in advance for any advice/help, Ellie.

[Sarah Edgington]

If I am understanding what you want correctly it's a one-liner.

bysort _pcon wave: egen mn_unemployed=mean(unemployed)

[Sven-Kristjan Bormann]

You could save the mean values in a new variable via

bysort _pcon wave: egen meanUnemp  = mean(unemployed)*100

[Ellie Daglish]

Thank you Sarah and Sven-Kristjan, both of your answers have worked perfectly. Many thanks.