Preparing data for multiple membership multilevel models in Stata

Question

I am trying to prepare my dataset for a multiple membership multilevel model, and am getting stuck on how to create a series of "multiple membership identifier" variables. Essentially, I have data with individual identifiers (ID) and the states that they lived in from 1996-1999 (state_1996 to state_1999), where 1=Alaska, 2=Arizona, etc. For example (this is made up):

ID state_1996 state_1997 state_1998 state_1999

1 1 1 2 2

2 1 1 1 1

3 3 1 1 1

n 4 4 4 4

And I am trying to create variables s1 through s51 that give the proportion of time each individual spent in each state. For example, based on the previous table and giving just a subset of these new variables, I would like something that looks like this:

ID s1 s2 s3 s4

1 0.5 0.5 0 0

2 1.0 0 0 0

3 0.75 0 0.25 0

n 0 0 0 1.0

Any help on the best way to do this would be much appreciated, thank you!

Cross-posted and answered at https://www.statalist.org/forums/forum/general-stata-discussion/general/1568974-preparing-data-for-multiple-membership-multilevel-models It's a good idea to tell people about cross-posting. — Nick Cox, Aug 18 '20 at 18:14

score 0 · Answer 1 · answered Aug 19 '20 at 03:25

Here's a very simple version that should give you what you need:

forv i = 1/51{
    gen s`i' = 0
    forv year = 1996/1999{
        replace s`i' = s`i' + 0.25 if state_`year' == `i'
    }
}

Here's a more general version that should work for an arbitrary number of states and years, in case it's useful for anyone:

// Count how many years there are
ds state_????
local yearVars `r(varlist)'
local numYears: list sizeof yearVars
di `numYears'

// What fraction of the total time is one year?
local myFrac = 1/`numYears'
di `myFrac'

// Get a list of all values state_???? can take
foreach var in `yearVars'{
    levelsof `var'
    local tempYears `r(levels)'
    local allStates: list allStates | tempYears
}

// Loop over all states to create the variables you want
foreach i in `allStates'{
    gen s`i' = 0
    foreach var in `yearVars'{
        replace s`i' = s`i' + `myFrac' if `var' == `i'
    }
    // Make sure you don't get any wacky values here
    assert s`i' >=0 & s`i' <= 1
}

// Make sure total time adds up to 1
egen totaltime = rowtotal(s? /* s?? */) // uncomment s?? if you've got between 10 and 99 states; I bet you do
assert totaltime == 1
drop totaltime

Preparing data for multiple membership multilevel models in Stata

1 Answers1