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 John Marvel To statalist@hsphsun2.harvard.edu Subject Re: st: Using the foreach command to solve a problem with multilevel data Date Wed, 17 Mar 2010 11:16:01 -0400

```Michael,

Thank you very much for your help.  Your code solved my problem!

Eric, thank you for your help as well (I haven't tried your code yet,
only because I
happened to try Michael's first).

John

On Wed, Mar 17, 2010 at 2:20 AM, Michael Norman Mitchell
<Michael.Norman.Mitchell@gmail.com> wrote:
> 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/
>

*
*   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/
```