Mean of observations contained in other variable

[Rodrigo Fernandes]

Dear Forum,
I have a variable, named "cbo02". I want to calculate the mean of the observations of this variable that are contained in other variable, "t". The "cbo02" variable has 51534752 observations and the "t" variable 132 because "t" is a category and I wanna see which of the "cbo02" observations are contained in "t" and make the mean of it.

cbo02 t
717020 214305
715210 214325
717020 214310
717020 214405
773125 214430
784205 214425
715525 214415
715210 214410
715525 214505
717020 142105
717020 214925
513205 214915
717020 214910
etc...

Thanks.

[Carlo Lazzaro]

Rodrigo:
welcome to this forum.
If -t- is a 132-level categorical variable that classifies -cbo02-, you may want to try:

tabstat cbo02, stat(count mean sd p50 min max) by(t)

[Rodrigo Fernandes]

Thank you, Carlo but I forgot to mention that I want to calculate the mean of other variable -s-. So, I want to calculate the mean of -s- of the observations -cbo02- that are contained in -t-. I don't know if it's easier to understand now.

[Carlo Lazzaro]

Rodrigo:
a temptative reply:

egen flag=group( cbo02 t)
tabstat s, stat(count mean sd p50 min max) by(flag)

[Rodrigo Fernandes]

Thank you, Carlo but I believe that this didn't work out. It calculated the mean of the firsts -cbo02- but not all -cbo02- that where equal to -t-.

  flag |         N      mean        sd       p50       min       max
---------+------------------------------------------------------------
       1 |         1   1088.82         .   1088.82   1088.82   1088.82
       2 |         1    923.27         .    923.27    923.27    923.27
       3 |         1     911.6         .     911.6     911.6     911.6
       4 |         1    787.99         .    787.99    787.99    787.99
       5 |         1   1051.57         .   1051.57   1051.57   1051.57
       6 |         1         0         .         0         0         0
       7 |         1   1101.42         .   1101.42   1101.42   1101.42
       8 |         1   2143.94         .   2143.94   2143.94   2143.94
       9 |         1   2198.94         .   2198.94   2198.94   2198.94
      10 |         1   3371.62         .   3371.62   3371.62   3371.62
      11 |         1   2784.42         .   2784.42   2784.42   2784.42
      12 |         1    931.31         .    931.31    931.31    931.31
      13 |         1   1390.59         .   1390.59   1390.59   1390.59
      14 |         1   1416.66         .   1416.66   1416.66   1416.66
      15 |         1      1300         .      1300      1300      1300
      16 |         1      1920         .      1920      1920      1920
      17 |         1   1252.62         .   1252.62   1252.62   1252.62
      18 |         1   1336.98         .   1336.98   1336.98   1336.98
      19 |         1   1611.96         .   1611.96   1611.96   1611.96
      20 |         1    469.61         .    469.61    469.61    469.61
      21 |         1   1409.53         .   1409.53   1409.53   1409.53
      22 |         1   1655.75         .   1655.75   1655.75   1655.75
      23 |         1   1041.79         .   1041.79   1041.79   1041.79
      24 |         1    816.67         .    816.67    816.67    816.67
      25 |         1   1090.26         .   1090.26   1090.26   1090.26
      26 |         1    952.47         .    952.47    952.47    952.47
      27 |         1    795.01         .    795.01    795.01    795.01
      28 |         1    711.58         .    711.58    711.58    711.58
      29 |         1    938.99         .    938.99    938.99    938.99
      30 |         1    593.47         .    593.47    593.47    593.47
      31 |         1    813.31         .    813.31    813.31    813.31
      32 |         1    811.91         .    811.91    811.91    811.91
      33 |         1    948.46         .    948.46    948.46    948.46
      34 |         1       871         .       871       871       871
      35 |         1    875.55         .    875.55    875.55    875.55
      36 |         1      1182         .      1182      1182      1182
      37 |         1    885.37         .    885.37    885.37    885.37
      38 |         1    901.12         .    901.12    901.12    901.12
      39 |         1    2183.6         .    2183.6    2183.6    2183.6
      40 |         1   1488.88         .   1488.88   1488.88   1488.88
      41 |         1   1155.24         .   1155.24   1155.24   1155.24
      42 |         1    856.28         .    856.28    856.28    856.28
      43 |         1   1025.99         .   1025.99   1025.99   1025.99
      44 |         1     832.4         .     832.4     832.4     832.4
---------+------------------------------------------------------------
   Total |        44  1200.681  605.6884   1033.89         0   3371.62
----------------------------------------------------------------------

And is the same as the mean of the 132 firsts observations.

[Carlo Lazzaro]

Rodrigo:
please post and example of your data that contains -s- variable, too. Thanks.

[Rodrigo Fernandes]

Here it is. -s- is the income of the observations.

cbo02    t    s
717020    214305    1090.26
715210    214325    931.31
717020    214310    952.47
717020    214405    795.01
773125    214430    1488.88
784205    214425    1025.99
715525    214415    1409.53
715210    214410    1390.59
715525    214505    1655.75
717020    142105    1041.79
717020    214925    593.47
513205    214915    1088.82
717020    214910    938.99
717020    214905    711.58
771105    142605    885.37
771105    212424    901.12
517420    214340    1051.57
715210    313105    1300
514320    313125    787.99
717020    314705    813.31
715505    316340    469.61
715210    316335    1611.96
715210    316310    1336.98
715210    316305    1252.62
774105    314730    856.28
717020    314715    948.46
717020    314720    871
717020    314710    811.91
715210    311510    1416.66
773125    316110    1155.24
514320    212405    911.6
773120    212420    2183.6
717020    212410    816.67
517420    391111    1101.42
752320    300305    1182
752305    313415    875.55
517420    313210    0
823325    313315    832.4
632410    313215    3371.62
632410    313205    2198.94
632410    300305    2143.94
632410    313415    2784.42
514225    313215    923.27
715210    313205    1920
517410              1651.62
724315              2005.01
712205              1205.11
715505              1552.39
715505              1346.42
313205              1591.89

[Carlo Lazzaro]

Rodrigo:
please check if what follows is what you've in mind:

. tabstat s if t!=., stat(count mean sd p50 min max) by(t)

Summary for variables: s
     by categories of: t

       t |         N      mean        sd       p50       min       max
---------+------------------------------------------------------------
  142105 |         1   1041.79         .   1041.79   1041.79   1041.79
  142605 |         1    885.37         .    885.37    885.37    885.37
  212405 |         1     911.6         .     911.6     911.6     911.6
  212410 |         1    816.67         .    816.67    816.67    816.67
  212420 |         1    2183.6         .    2183.6    2183.6    2183.6
  212424 |         1    901.12         .    901.12    901.12    901.12
  214305 |         1   1090.26         .   1090.26   1090.26   1090.26
  214310 |         1    952.47         .    952.47    952.47    952.47
  214325 |         1    931.31         .    931.31    931.31    931.31
  214340 |         1   1051.57         .   1051.57   1051.57   1051.57
  214405 |         1    795.01         .    795.01    795.01    795.01
  214410 |         1   1390.59         .   1390.59   1390.59   1390.59
  214415 |         1   1409.53         .   1409.53   1409.53   1409.53
  214425 |         1   1025.99         .   1025.99   1025.99   1025.99
  214430 |         1   1488.88         .   1488.88   1488.88   1488.88
  214505 |         1   1655.75         .   1655.75   1655.75   1655.75
  214905 |         1    711.58         .    711.58    711.58    711.58
  214910 |         1    938.99         .    938.99    938.99    938.99
  214915 |         1   1088.82         .   1088.82   1088.82   1088.82
  214925 |         1    593.47         .    593.47    593.47    593.47
  300305 |         2   1662.97  680.1943   1662.97      1182   2143.94
  311510 |         1   1416.66         .   1416.66   1416.66   1416.66
  313105 |         1      1300         .      1300      1300      1300
  313125 |         1    787.99         .    787.99    787.99    787.99
  313205 |         2   2059.47  197.2403   2059.47      1920   2198.94
  313210 |         1         0         .         0         0         0
  313215 |         2  2147.445  1731.245  2147.445    923.27   3371.62
  313315 |         1     832.4         .     832.4     832.4     832.4
  313415 |         2  1829.985  1349.775  1829.985    875.55   2784.42
  314705 |         1    813.31         .    813.31    813.31    813.31
  314710 |         1    811.91         .    811.91    811.91    811.91
  314715 |         1    948.46         .    948.46    948.46    948.46
  314720 |         1       871         .       871       871       871
  314730 |         1    856.28         .    856.28    856.28    856.28
  316110 |         1   1155.24         .   1155.24   1155.24   1155.24
  316305 |         1   1252.62         .   1252.62   1252.62   1252.62
  316310 |         1   1336.98         .   1336.98   1336.98   1336.98
  316335 |         1   1611.96         .   1611.96   1611.96   1611.96
  316340 |         1    469.61         .    469.61    469.61    469.61
  391111 |         1   1101.42         .   1101.42   1101.42   1101.42
---------+------------------------------------------------------------
   Total |        44  1200.681  605.6884   1033.89         0   3371.62
----------------------------------------------------------------------

.