Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
From | John Marvel <jdmarvel@gmail.com> |
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 > (note: file persons.dta not found) > 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 > (10 real changes made) > > . * 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 > (note: file personmean.dta not found) > 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/