verifying a condition for three consecutive years in panel data

[Aicha SA]

Hello everyone, this is my first post here and I'm relatively new to Stata so I was hoping to get some help on an issue I am stuck with.

I have a large dataset (panel data) of different corporations and I want to code it so as to verify a condition: ratio3==1 for three consecutive years as those are the companies I am interested in. I would then drop all the others for which the condition doesn't hold for three years in a row.

Could you please help? Any insight would be greatly appreciated!

Thanks!

[Nick Cox]

tsset id time
gen spell  = (L1.ratio3 == 1) & (ratio3 == 1) & (F1.ratio3 == 1)
bysort id (spell): keep if spell[_N] == 1

[Aicha SA]

Thanks a lot! The problem (that I hadn't thought about) is that I have multiple closing years per ID, which produces an error with tsset. How could I: 1) ask stata to collapse(sum) by id but keeping only the first year identifier? and 2) I also need to count all the firms for which ratio3==0... Any leads?
Many thanks again!

[Nick Cox]

I can’t see how a spell of identical values can be defined at all if you have replicated values.

[Aicha SA]

Thanks, Nick. Yes that's the issue.

What I have done so far is I have created max(yearClosed) to keep the last year per ID and replace the year variable, and since I still have replicated values (as I have two company types), generated a check variable as follows:

bysort ratio3 yearClosed: gen check=1 if ratio3[_n]==1 & ratio3[_n+1]==1 & ratio3[_n+2]==1

then a total variable of those companies by sector and closing year.. It seems to work though not sure if there is a less roundabout way of doing this.

Thanks again!