Tag and count observations between range by group

[Klaus Klausen]

Hi,

I fear I have a quite easy to solve problem but couldn't find a way to do so yet.

Example data:

* Example generated by -dataex-. For more info, type help dataex
clear
input int Deal_No str8 Acquirer str7 date float return byte beginning_end
123 "ABC Corp" "01/2000"  .004 .
123 "ABC Corp" "02/2000"  -.05 .
123 "ABC Corp" "03/2000"  .011 1
123 "ABC Corp" "04/2000"  .011 .
123 "ABC Corp" "05/2000" -.012 .
123 "ABC Corp" "06/2000"  -.05 1
123 "ABC Corp" "07/2000"  .004 .
321 "DEF Inc"  "01/2000"  .004 .
321 "DEF Inc"  "02/2000"  -.05 1
321 "DEF Inc"  "03/2000"  .011 .
321 "DEF Inc"  "04/2000"  .011 .
321 "DEF Inc"  "05/2000" -.012 .
321 "DEF Inc"  "06/2000"  -.05 1
321 "DEF Inc"  "07/2000"   .95 .
321 "DEF Inc"  "08/2000" .0032 .
321 "DEF Inc"  "09/2000" .0523 .
end

The variable Deal_No represents an ID that identifies a merger with the Acquirer being the bidding company and it's specific return at the end of a given month. Think of the beginning_end variable as one that indicates a starting and end point for each Deal_No. So each group of Deal_No has exact two entries with the value 1 in the beginning_end variable; the first "1" indicating the starting point and the second "1" the ending point. I am only interested in the observations that fall between these starting and end points (i.e., between the first and second "1" in each group) in terms of time by Deal. Specifically, I would like to replace the missing values that fall between the start and end point of a deal with "1" and generate a variable that counts the number of observations with a "1".

Ultimately, the final result should look like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input int Deal_No str8 Acquirer str7 date float return byte beginning_end float counter
123 "ABC Corp" "01/2000"  .004 . .
123 "ABC Corp" "02/2000"  -.05 . .
123 "ABC Corp" "03/2000"  .011 1 4
123 "ABC Corp" "04/2000"  .011 1 4
123 "ABC Corp" "05/2000" -.012 1 4
123 "ABC Corp" "06/2000"  -.05 1 4
123 "ABC Corp" "07/2000"  .004 . .
321 "DEF Inc"  "01/2000"  .004 . .
321 "DEF Inc"  "02/2000"  -.05 1 5
321 "DEF Inc"  "03/2000"  .011 1 5
321 "DEF Inc"  "04/2000"  .011 1 5
321 "DEF Inc"  "05/2000" -.012 1 5
321 "DEF Inc"  "06/2000"  -.05 1 5
321 "DEF Inc"  "07/2000"   .95 . .
321 "DEF Inc"  "08/2000" .0032 . .
321 "DEF Inc"  "09/2000" .0523 . .
end

Any help is much appreciated

[Nick Cox]

clear
input int Deal_No str8 Acquirer str7 date float return byte beginning_end
123 "ABC Corp" "01/2000"  .004 .
123 "ABC Corp" "02/2000"  -.05 .
123 "ABC Corp" "03/2000"  .011 1
123 "ABC Corp" "04/2000"  .011 .
123 "ABC Corp" "05/2000" -.012 .
123 "ABC Corp" "06/2000"  -.05 1
123 "ABC Corp" "07/2000"  .004 .
321 "DEF Inc"  "01/2000"  .004 .
321 "DEF Inc"  "02/2000"  -.05 1
321 "DEF Inc"  "03/2000"  .011 .
321 "DEF Inc"  "04/2000"  .011 .
321 "DEF Inc"  "05/2000" -.012 .
321 "DEF Inc"  "06/2000"  -.05 1
321 "DEF Inc"  "07/2000"   .95 .
321 "DEF Inc"  "08/2000" .0032 .
321 "DEF Inc"  "09/2000" .0523 .
end

gen mdate = monthly(date, "MY")
format mdate %tm 

bysort Deal_No (mdate) : gen ones = sum(beginning_end) 
replace beginning_end = 1 if ones == 1 & missing(beginning_end) 

egen first2 = min(cond(ones == 2, mdate, .)), by(Deal_No)
replace ones = 1 if mdate == first2 

bysort Deal_No ones: gen counter = _N  if ones == 1 

list, sepby(Deal_No ones)

     +-------------------------------------------------------------------------------------+
     | Deal_No   Acquirer      date   return   beginn~d    mdate   ones   first2   counter |
     |-------------------------------------------------------------------------------------|
  1. |     123   ABC Corp   01/2000     .004          .   2000m1      0      485         . |
  2. |     123   ABC Corp   02/2000     -.05          .   2000m2      0      485         . |
     |-------------------------------------------------------------------------------------|
  3. |     123   ABC Corp   03/2000     .011          1   2000m3      1      485         4 |
  4. |     123   ABC Corp   04/2000     .011          1   2000m4      1      485         4 |
  5. |     123   ABC Corp   05/2000    -.012          1   2000m5      1      485         4 |
  6. |     123   ABC Corp   06/2000     -.05          1   2000m6      1      485         4 |
     |-------------------------------------------------------------------------------------|
  7. |     123   ABC Corp   07/2000     .004          .   2000m7      2      485         . |
     |-------------------------------------------------------------------------------------|
  8. |     321    DEF Inc   01/2000     .004          .   2000m1      0      485         . |
     |-------------------------------------------------------------------------------------|
  9. |     321    DEF Inc   02/2000     -.05          1   2000m2      1      485         5 |
 10. |     321    DEF Inc   03/2000     .011          1   2000m3      1      485         5 |
 11. |     321    DEF Inc   04/2000     .011          1   2000m4      1      485         5 |
 12. |     321    DEF Inc   05/2000    -.012          1   2000m5      1      485         5 |
 13. |     321    DEF Inc   06/2000     -.05          1   2000m6      1      485         5 |
     |-------------------------------------------------------------------------------------|
 14. |     321    DEF Inc   07/2000      .95          .   2000m7      2      485         . |
 15. |     321    DEF Inc   08/2000    .0032          .   2000m8      2      485         . |
 16. |     321    DEF Inc   09/2000    .0523          .   2000m9      2      485         . |
     +-------------------------------------------------------------------------------------+

[Klaus Klausen]

This works just perfect, thank you Nick.