How to replace value of a variable in one round with its value in a previous round ?

[Titir Bhattacharya]

Hi all,

Please consider the following sample data

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id round location)
1 1 1
1 2 1
1 3 1
2 1 2
2 2 2
2 3 4
3 1 3
3 2 4
3 3 3
end

In this panel data, location for ids 2 and 3 are not consistent across rounds. I want to make these locations consistent across rounds and equal to the location in round 1. So, location for id 2 would be 2 in all rounds, and for id 3 it would be 3 for all rounds.

I tried

sort id round
by id: replace location=location[_n-1]|location[_n-2] if location!=location[_n-1]|location!=location[_n-2]

but this simply converted all the locations to 1, which is the first location of id 1.


Would appreciate if someone could suggest how to go about this problem.

Thanks

[Øyvind Snilsberg]

bys id (round): replace location = location[1]

[Titir Bhattacharya]

bys id (round): replace location = location[1]

Thanks a lot for your help. I just needed some clarifications:

(1) does the [1] in location[1] correspond to round 1? If so, had I wanted the values to be equal to that of round 2, should I have used location[2] instead?
(2) if another round is added at the start of the series, such that the data looks like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id round location)
1 1 1
1 2 1
1 3 1
1 4 1
2 1 1
2 2 2
2 3 2
2 4 4
3 1 2
3 2 3
3 3 4
3 4 3
end

and I wanted the value of location be equal to that in round 2 for rounds after 2nd round but keep the value in 1st round unchanged, what modification would you suggest to the code?
Thanks

[Nick Cox]

Several issues arise here, ranging from Stata principle to whether you have balanced or unbalanced panels.

If you have a balanced panel dataset in which all panels have observations for rounds 1 to 4, then after

bysort id (round) :

the subscripts 1 2 3 4 will correspond exactly with round 1 2 3 4.

If you have any incomplete panels, you may need technique like this

bysort id : egen value2 = mean(cond(round == 2, value, .))

or like this

gen value2 = value if round == 2
bysort id (value2) : replace value2 = value2[1]

[Titir Bhattacharya]

Several issues arise here, ranging from Stata principle to whether you have balanced or unbalanced panels.

If you have a balanced panel dataset in which all panels have observations for rounds 1 to 4, then after

bysort id (round) :

the subscripts 1 2 3 4 will correspond exactly with round 1 2 3 4.

If you have any incomplete panels, you may need technique like this

bysort id : egen value2 = mean(cond(round == 2, value, .))

or like this

gen value2 = value if round == 2
bysort id (value2) : replace value2 = value2[1]

Thanks Nick, for your response. However, it seems both of these codes for unbalanced panel is changing the location for round 1 as well. I would have liked to keep the location in round 1 unchanged, while those in round 2 onward are made consistent with round 2 value

value of location be equal to that in round 2 for rounds after 2nd round but keep the value in 1st round unchanged

After obtaining value2 as per your suggested codes, I did

replace value2= value if round==1

to return round 1 value to original.

Thanks again for your help!

[Titir Bhattacharya]

Several issues arise here, ranging from Stata principle to whether you have balanced or unbalanced panels.

If you have a balanced panel dataset in which all panels have observations for rounds 1 to 4, then after

bysort id (round) :

the subscripts 1 2 3 4 will correspond exactly with round 1 2 3 4.

If you have any incomplete panels, you may need technique like this

bysort id : egen value2 = mean(cond(round == 2, value, .))

or like this

gen value2 = value if round == 2
bysort id (value2) : replace value2 = value2[1]

Hi Nick, apologies for starting this thread again. Some new problems have come up with regards to this issue and I'm now realising my example data was not entirely representative of my original data. Also, it appears I had not framed my question correctly which probably led to this problem. I request you to consider the following example data which I'm hoping is much more representative

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id round location)
1 1 1
1 2 1
1 3 1
1 4 2
1 5 2
2 1 2
2 2 2
2 4 2
2 5 3
3 1 4
3 2 3
3 3 3
3 5 4
4 1 2
4 2 2
4 5 5
end

I would like to generate a location2 variable such that location values for rounds 3,4,5 are same. If in the process, values for rounds 1 and 2 do get changed, I can change them back to the original. Now for rounds 3,4,5, the value needs to be equal to that of round 3, if round 3 is present. However, as you rightly pointed out the issue of unbalanced panel, if round 3 does not exist for an id, then for rounds 4,5 the value should be equal to that of round 4. If neither rounds 3,4 exist, then value of 5 would be left as it is. Ideally, i would like my data to look like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id round location location2)
1 1 1 1
1 2 1 1
1 3 1 1
1 4 2 1
1 5 2 1
2 1 2 2
2 2 2 2
2 4 2 2
2 5 3 2
3 1 4 4
3 2 3 3
3 3 3 3
3 5 4 3
4 1 2 2
4 2 2 2
4 5 5 5
end

Apologies once again for your inconvenience. I would greatly appreciate if you could help me figure this out.

Thanks

[Nick Cox]

Sounds like this:

gen location2 = location 

forval r = 3/5 { 
        bysort id :  egen value`r' = mean(cond(round == `r', location, .)) 
} 

replace location2 = cond(value3 < ., value3, cond(value4 < ., value4, value5)) if round >= 3

[Titir Bhattacharya]

Sounds like this:

gen location2 = location

forval r = 3/5 {
bysort id : egen value`r' = mean(cond(round == `r', location, .))
}

replace location2 = cond(value3 < ., value3, cond(value4 < ., value4, value5)) if round >= 3

This worked perfectly! Thanks a lot Nick