Gen a variable in a loop: error "variable already defined"

[Linda Pasi]

Hello everyone.

I am trying to run this loop. I need to get two subsamples of frequent and occasional acquirers. the first one represents firms that made at least 2 deals over a 3 year window and the second one firms that made at least 5 deals over a 3 year window. My sample period is 1990-2011.

This is what I have so far but STATA gives me error "variable count already defined" and I can't figure out how to solve this.

Does anyone have any advice?

Thanks in advance!

forval year = 1990(1)2011{
keep if year >= `year'
keep if year <= `year' + 3
gen count =.
replace count = 1
collapse (sum) count (first) year, by(AcquirorCUSIP)
keep if count > 2
if `year' > 1990{
append using frequent_acq.dta
}
save frequent_acq.dta, replace
}

[Linda Pasi]

I also tried this code and it gives me "no observations" error.

forval year = 1990(1)2011{
keep if year >= `year'
keep if year <= `year' + 3
collapse (sum) count (first) year, by(AcquirorCUSIP)
keep if count > 2
if `year' > 1990{
append using frequent_acq.dta
}
save frequent_acq.dta, replace
}

[Nick Cox]

#1 Stata is telling you the error. Second time around the loop, count already exists, so your generate statement fails. You cannot generate an existing variable. However, you have bigger problems, as once you collapse your main dataset has gone.

For a rolling window, rangestat from SSC would imply code something like this

gen count = 1
rangestat (sum) count, interval(year 0 3) by(AcquirorCUSIP)

after which you can choose subsets at will.

#2 appears to suffer also from trying to collapse an already collapsed dataset. Just occasionally, the remedy is to read in the original dataset once again, but not here.

[Linda Pasi]

Dear Nick,

Thank you! I will try this.

Kind regards,
Linda