Find attrition at personal level

[Marta Oliveira]

Dear Stata users,
I am using an unbalanced panel dataset. Here is an example of my dataset.

hhid	pid	year	attrition
1	1	2000	Successful
1	1	2001	Successful
1	.	2002	moved out of country
1	1	2003	Successful
2	2	2001	Successful
2	2	2002	Successful

hhid is household id, pid personal id. I only have information of survey attrition (if the interview was successful or not) at the household level. What I want to create is a variable that indicates me that the individual in the next year will "move out of the country", or that, in this case, the individual 1 in 2002 is not in the country. Beware that an individual can leave the country and return, and continue to participate in the survey in 2003 (but does not participate in the survey when it is out of the country). Could any one help me?
I am using Stata 14.

[David Benson]

Hi Marta,

It's not clear to me what you want to do? Could you also post a sample of what you would like the dataset to look like when you are done (and provide a couple more examples?). Is it important that pid==. when the person is out of the country, but it is the same PID in the year before and year after?

[Marta Oliveira]

The hhid and pid never change, no matter the individual leaves the country and returns or not. In each household might live more than 1 individual. I only have information about attrition at the household level, which was in one file, and I merged the household data with the personal data, and obtained a dataset similar to what I showed initially.
Here is an example of what I want to obtain:

hhid	pid	year	attrition	moveout
1	1	2000	Successful	0
1	1	2001	Successful	1
1	.	2002	moved out of country	.
1	1	2003	Successful	1
1	.	2004	moved out of country	.
2	2	2001	Successful	0
2	2	2002	Successful	0
2	2	2003	Successful	1
2	.	2004	moved out of country	.
3	3	2000	Successful	0
3	4	2000	Successful	0

or in alternative, it can be:

hhid	pid	year	attrition	movedout
1	1	2000	Successful	0
1	1	2001	Successful	0
1	1	2002	moved out of country	1
1	1	2003	Successful	0
1	1	2004	moved out of country	1
2	2	2001	Successful	0
2	2	2002	Successful	0
2	2	2003	Successful	0
2	2	2004	moved out of country	1
3	3	2000	Successful	0
3	4	2000	Successful	0

[David Benson]

Hi Marta,

1) It would be helpful if you could share a few more of your observations (because you have multiple individuals per household, and I want to make sure the code will work in that case). Are you able to install dataex to share data? (You mention that you are on Stata 14 -- dataex works for Stata 14.2 and above). (Also, if you could put your code delimiters, it would be *much* easier to read). If you need help with that, I created a short Youtube video on dataex and code delimiters here. (I made it too long--feel free to watch at 2x speed, and you may only need the first 6 minutes) .

2) You may also want to take a look at these posts here, here, here, and here (they all involve coding stuff based on hhid)

dataex hhid pid year attrition, count(50)
input byte(hhid pid) int year str20 attrition
1 1 2000 "Successful"          
1 1 2001 "Successful"          
1 . 2002 "moved out of country"
1 1 2003 "Successful"          
1 . 2004 "moved out of country"
2 2 2001 "Successful"          
2 2 2002 "Successful"          
2 2 2003 "Successful"          
2 . 2004 "moved out of country"
3 3 2000 "Successful"          
3 4 2000 "Successful"          
end

. list, noobs sepby(hhid)

  +------------------------------------------+
  | hhid   pid   year              attrition |
  |------------------------------------------|
  |    1     1   2000             Successful |
  |    1     1   2001             Successful |
  |    1     .   2002   moved out of country |
  |    1     1   2003             Successful |
  |    1     .   2004   moved out of country |
  |------------------------------------------|
  |    2     2   2001             Successful |
  |    2     2   2002             Successful |
  |    2     2   2003             Successful |
  |    2     .   2004   moved out of country |
  |------------------------------------------|
  |    3     3   2000             Successful |
  |    3     4   2000             Successful |
  +------------------------------------------+

// Filling in the missing PID.  Not sure this will work correctly if multiple PID's in household
replace pid = pid[_n-1] if pid[_n-1]!=. & hhid==hhid[_n-1]  

// Creating the movedout variable
gen movedout = (attrition=="moved out of country")

// m_prior is "1 if they moved out of country in the PRIOR year"
bysort hhid (pid year): gen m_prior = (attrition[_n-1]== "moved out of country")

// m_next is "1 if they moved out of country in the NEXT year"
bysort hhid (pid year): gen m_next = (attrition[_n+1]== "moved out of country")

// Counting number of years member was out of the country
egen count_out = total(movedout), by(hhid)

// 1 if member was EVER out of the country
gen ever_out = (count_out >=1 & count_out<.)

. list, noobs sepby(hhid)

  +----------------------------------------------------------------------------------------------+
  | hhid   pid   year              attrition   movedout   m_prior   m_next   count_~t   ever_out |
  |----------------------------------------------------------------------------------------------|
  |    1     1   2000             Successful          0         0        0          2          1 |
  |    1     1   2001             Successful          0         0        1          2          1 |
  |    1     1   2002   moved out of country          1         0        0          2          1 |
  |    1     1   2003             Successful          0         1        1          2          1 |
  |    1     1   2004   moved out of country          1         0        0          2          1 |
  |----------------------------------------------------------------------------------------------|
  |    2     2   2001             Successful          0         0        0          1          1 |
  |    2     2   2002             Successful          0         0        0          1          1 |
  |    2     2   2003             Successful          0         0        1          1          1 |
  |    2     2   2004   moved out of country          1         0        0          1          1 |
  |----------------------------------------------------------------------------------------------|
  |    3     3   2000             Successful          0         0        0          0          0 |
  |    3     3   2000             Successful          0         0        0          0          0 |
  +----------------------------------------------------------------------------------------------+