Question about generate new variables conditioning on other variables

[Liang Wang Jack]

Dear all,

I have a question about generating a new variable "movingindicator". The dataset variables are id, countycode (=25 means live in Genesee County). How can I generate movingindicator by countycode which,
if she lives in Genesee and then moves out, then generate movingindicator = 1
if she lives out of Genesee County and then moves in, then generate movingindicator = 2
if she lives in Genesee and then moves out and then moves in, then generate movingindicator = 3
if she lives in out of Genesee County and then moves in and then moves out, then generate movingindicator = 3
Please note,
the individual may have a different number of observations,
the individual may have the different number of countycode = 25, for example, id = 1 has 1 row countycode = 25, id =5 has 3 rows countycode = 25, but movingindicator for them are all 1.

Here is the dataset below,

 
 * Example generated by -dataex-. To install: ssc install dataex clear input float(id countycode) 1 25 1 26 1 98 2 69 2 72 2 88 2 25 2 25 2 25 3 25 3 29 3 25 3 40 4 60 4 25 4 49 5 25 5 25 5 25 5  4 6 49 6 82 6 25 end

Here is the dataset that I expect,



I spent the whole afternoon working on this, but I didn't solve it. I appreciate if someone can help.

Best regards,

Jack Liang

[Bruce Weaver]

Hi Jack. I don't have time to work on your problem right now, but I did take a couple minutes to fix up your code for reading in the sample data. Here it is:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id countycode)
 1  25
 1  26
 1  98
 2  69
 2  72
 2  88
 2  25
 2  25
 2  25
 3  25
 3  29
 3  25
 3  40
 4  60
 4  25
 4  49
 5  25
 5 25
 5 25
 5  4
 6 49
 6 82
 6 25
end

/*
if lives in 25 and then moves out, movingindicator = 1
if lives out of 25 and then moves in, movingindicator = 2
if lives in 25 then moves out and then moves in, movingindicator = 3
if lives out of 25 and then moves in and then moves out, movingindicator = 3
Please note,
the individual may have a different number of observations,
the individual may have the different number of countycode = 25,
for example, id = 1 has 1 row countycode = 25, id =5 has 3 rows
countycode = 25, but movingindicator for them are all 1.
*/

[David Beheshti]

I think this works:

gen movingindicator = .

bysort id: gen t = _n

xtset id t

gen county_l1 = L.countycode
gen county_f1 = F.countycode

replace movingindicator = 1 if countycode==25 & F.countycode!=25 & F.countycode!=.
replace movingindicator = 2 if countycode!=25 & F.countycode==25

bysort id: egen temp = mean(movingindicator)
replace temp = 3 if !inlist(temp, 1, 2)
replace movingindicator=temp

[Liang Wang Jack]

Hi Jack. I don't have time to work on your problem right now, but I did take a couple minutes to fix up your code for reading in the sample data. Here it is:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id countycode)
1 25
1 26
1 98
2 69
2 72
2 88
2 25
2 25
2 25
3 25
3 29
3 25
3 40
4 60
4 25
4 49
5 25
5 25
5 25
5 4
6 49
6 82
6 25
end

/*
if lives in 25 and then moves out, movingindicator = 1
if lives out of 25 and then moves in, movingindicator = 2
if lives in 25 then moves out and then moves in, movingindicator = 3
if lives out of 25 and then moves in and then moves out, movingindicator = 3
Please note,
the individual may have a different number of observations,
the individual may have the different number of countycode = 25,
for example, id = 1 has 1 row countycode = 25, id =5 has 3 rows
countycode = 25, but movingindicator for them are all 1.
*/

Thank you very much for your editing.

[Liang Wang Jack]

I think this works:

Hi David, Thanks for your response, this code works well. I check Stata help for xtset, but I still couldn't understand the meaning of xtset here. Could you explain a little bit for this xtset? Thanks.

[Liang Wang Jack]

I think this works:

Also, after create movingindicator, can I use other commands to transfer this panel data to my original data, since we use xtset to change it to panel data?

[David Beheshti]

Hi Liang,

You use -xtset panelvar timevar- to tell Stata that you are working with panel data (repeated observations over time for individuals or groups, e.g., annual observations for multiple countries). So what I did here was generate a variable "t" as a kind of time variable and then tell Stata you were working with a panel dataset. Among other things, this lets you create leads and lags of your variables, which then allowed me to fill in the "movingindicator" variable by comparing the current value of countycode with past and future values to see if people moved.

[Liang Wang Jack]

Hi Liang,

You use -xtset panelvar timevar- to tell Stata that you are working with panel data (repeated observations over time for individuals or groups, e.g., annual observations for multiple countries). So what I did here was generate a variable "t" as a kind of time variable and then tell Stata you were working with a panel dataset. Among other things, this lets you create leads and lags of your variables, which then allowed me to fill in the "movingindicator" variable by comparing the current value of countycode with past and future values to see if people moved.

Do I have to use other command to transfer this panel data back to my original data?

Thanks,

Jack Liang

[David Beheshti]

Do I have to use other command to transfer this panel data back to my original data?

Sorry, I'm not sure what you mean? All of the commands I wrote just add to your original data, you shouldn't have to transfer anything. Can you try to elaborate on your question a bit?

[Liang Wang Jack]

Sorry, I'm not sure what you mean? All of the commands I wrote just add to your original data, you shouldn't have to transfer anything. Can you try to elaborate on your question a bit?

Sure. I mean, I am not sure my the style of my original data, but we use xtset to transfer my original data to panel data. I am considering whether should we transfer this panel data back to the style of my original data. Hope this explanation is more clear. Thanks.

[David Beheshti]

OK, I think I understand. The command -xtset, clear- will undo the panel setting.

[Liang Wang Jack]

OK, I think I understand. The command -xtset, clear- will undo the panel setting.

Awesome, thank you very much and have a good day!