Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.

# Re: st: Using the foreach command to solve a problem with multilevel data

 From Michael Norman Mitchell To statalist@hsphsun2.harvard.edu Subject Re: st: Using the foreach command to solve a problem with multilevel data Date Tue, 16 Mar 2010 23:20:03 -0700

Dear John

Thanks for explaining your problem so clearly. I am thinking that the solution is to use the collapse command to create the mean age by group and authority, then subtract one from the authority score, and then match merge that back to the original data set. Then a person with authority of 1 would get merged with the mean score of authority (2-1) because of the subtraction.

Here is my sample code and output...

. clear

. input groupid        subjectid   authority    age     meanage

groupid  subjectid  authority        age    meanage
1. 1                      1            1            25           26
2. 1                      2            1            25           26
3. 1                      3            1            28           26
4. 1                      4            2            28           28.5
5. 1                      5            2            29           28.5
6. 1                      6            3            34           34
7. 1                      7            3            34           34
8. 1                      8            4            40           42
9. 1                      9            4            44           42
10. 1                     10           5            50           50
11. 2                     11           1            23           23.5
12. 2                     12           1            24           23.5
13. 2                     13           2            30           31
14. 2                     14           2            31           31
15. 2                     15           2            32           31
16. 2                     16           3            36           37
17. 2                     17           3            38           37
18. 2                     18           4            44           46
19. 2                     19           4            48           46
20. 2                     20           5            50           51
21. 2                     21           5            52           51
22. end

.
. save persons, replace
file persons.dta saved

.
. use persons, clear

.
. * create mean age by group and authority level
. collapse age, by(groupid authority)

. * rename age to mage
. rename age mage

. * make authority equal to one less than authority
. replace authority = authority-1

. * show what we have
. list, sepby(groupid) abb(30)

+----------------------------+
| groupid   authority   mage |
|----------------------------|
1. |       1           0     26 |
2. |       1           1   28.5 |
3. |       1           2     34 |
4. |       1           3     42 |
5. |       1           4     50 |
|----------------------------|
6. |       2           0   23.5 |
7. |       2           1     31 |
8. |       2           2     37 |
9. |       2           3     46 |
10. |       2           4     51 |
+----------------------------+

. * save mean scores
. save personmean, replace
file personmean.dta saved

.
. * merge persons with mean
. use persons

. merge m:1 groupid authority using meanxyz

Result                           # of obs.
-----------------------------------------
not matched                             5
from master                         3  (_merge==1)
from using                          2  (_merge==2)

matched                                18  (_merge==3)
-----------------------------------------

.
. * show results
. sort groupid subjectid authority

. list , abb(30)

+--------------------------------------------------------------------------+ | groupid subjectid authority age meanage mage _merge | |--------------------------------------------------------------------------| 1. | 1 1 1 25 26 28.5 matched (3) | 2. | 1 2 1 25 26 28.5 matched (3) | 3. | 1 3 1 28 26 28.5 matched (3) | 4. | 1 4 2 28 28.5 34 matched (3) | 5. | 1 5 2 29 28.5 34 matched (3) | |--------------------------------------------------------------------------| 6. | 1 6 3 34 34 42 matched (3) | 7. | 1 7 3 34 34 42 matched (3) | 8. | 1 8 4 40 42 50 matched (3) | 9. | 1 9 4 44 42 50 matched (3) | 10. | 1 10 5 50 50 . master only (1) | |--------------------------------------------------------------------------| 11. | 1 . 0 . . 26 using only (2) | 12. | 2 11 1 23 23.5 31 matched (3) | 13. | 2 12 1 24 23.5 31 matched (3) | 14. | 2 13 2 30 31 37 matched (3) | 15. | 2 14 2 31 31 37 matched (3) | |--------------------------------------------------------------------------| 16. | 2 15 2 32 31 37 matched (3) | 17. | 2 16 3 36 37 46 matched (3) | 18. | 2 17 3 38 37 46 matched (3) | 19. | 2 18 4 44 46 51 matched (3) | 20. | 2 19 4 48 46 51 matched (3) | |--------------------------------------------------------------------------| 21. | 2 20 5 50 51 . master only (1) | 22. | 2 21 5 52 51 . master only (1) | 23. | 2 . 0 . . 23.5 using only (2) | +--------------------------------------------------------------------------+

Note the resulting mismatches... authority level 5 is a mismatch because there is no authority level 6, and authority level 0 is mismatched as well. The _merge variable identifies these and you can discard them at your pleasure.

I hope that is useful.

Best luck!

Michael N. Mitchell
See the Stata tidbit of the week at...
http://www.MichaelNormanMitchell.com

On 2010-03-16 10.58 PM, John Marvel wrote:
I have data on individuals ("subjectid") nested in groups ("groupid").
Within groups, individuals have varying levels of authority
("authority"), ranging from a low of 1 to a high of 5.  I also have
data on each individual's age ("age").  I have created a variable
called "meanage," which equals the within-group mean of age for each
authority level:

bysort groupid authority: egen meanage = mean(age)

What I want to do is create a new variable (call it "meanageup") that,
for individual i (in group g) with authority level a, is equal to
"meanage" for authority level a + 1 (in group g).  In other words, if an
individual in group x has an authority level of 1, I want "meanageup"
for that individual to equal the mean age of all group x individuals
who have an authority level of 2.  And if an individual in group x has
an authority level of 2, I want "meanageup" for that individual to
equal the mean age of all group x individuals who have an authority
level of 3, etc.

For an idea of what the data look like, see below.
Thus, for subjectids 1, 2, and 3, I want "meanageup" to equal 28.5;
for subjectids 4 and 5, I want "meanageup" to equal 34, etc.  Because
the data set is large, I'd like to automate this as much as possible.
(It's not clear to me how I would do this with the foreach
command, and I haven't been able to track down an
answer in the statalist archives or elsewhere online).

groupid        subjectid   authority    age     meanage
1                      1            1            25           26
1                      2            1            25           26
1                      3            1            28           26
1                      4            2            28           28.5
1                      5            2            29           28.5
1                      6            3            34           34
1                      7            3            34           34
1                      8            4            40           42
1                      9            4            44           42
1                     10           5            50           50
2                     11           1            23           23.5
2                     12           1            24           23.5
2                     13           2            30           31
2                     14           2            31           31
2                     15           2            32           31
2                     16           3            36           37
2                     17           3            38           37
2                     18           4            44           46
2                     19           4            48           46
2                     20           5            50           51
2                     21           5            52           51

I would greatly appreciate any suggestions.

Thank you very much,

John
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/