Consecutive observations

[River Huang]

Dear all,

Suppose I have the following data

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 id float(m1995 m1996 m1997 m1998 m1999 m2000 m2001 m2002 m2003 m2004 c1 c2 c3)
"A" 0 1 1 1 0 1 1 0 1 1 3 2 2
"B" 0 0 0 1 1 1 0 1 . . 3 1 .
"C" 0 1 1 1 1 1 0 1 1 0 5 2 .
"D" 0 1 1 1 1 0 0 0 1 0 4 1 .
end

For each id, I want to count the number of consecutive observations and save the frequency as c1, c2, c3 and so on. Take id=A as an example, there are 3 consecutive 1s (from m1996-m1998) so c1 is 3. Similarly, from m2000-m2001, we have another variable c2 denoting 2 consecutive 1s. And the consecutive observations are from m2003-m2004, so c3=2. Any suggestion for how to do it?

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 id float(m1995 m1996 m1997 m1998 m1999 m2000 m2001 m2002 m2003 m2004)
"A" 0 1 1 1 0 1 1 0 1 1
"B" 0 0 0 1 1 1 0 1 . .
"C" 0 1 1 1 1 1 0 1 1 0
"D" 0 1 1 1 1 0 0 0 1 0
end

//    SAVE A COPY OF THE ORIGINAL DATA
tempfile copy
save `copy'


//    GO LONG
reshape long m, i(id) j(_j)
// IDENTIFY SPELLS OF CONSECUTIVE REPEATS AND CALCULATE LENGTH
by id (_j), sort: gen spell = sum(m != m[_n-1])
by id spell (_j), sort: gen c = _N
//    PRESERVE ONLY SPELLS OF ONES
drop if m == 0 | missing(m)
//  GO TO ONE OBSERVATION PER SPELL    
by id spell: keep if _n == 1
//    NUMBER THEM SEQUENTIALLY
by id: replace _j = _n
//    CLEAN UP AND RETURN TO WIDE
drop spell m
reshape wide c, i(id) j(_j)
merge 1:m id using `copy'

[River Huang]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 id float(m1995 m1996 m1997 m1998 m1999 m2000 m2001 m2002 m2003 m2004)
"A" 0 1 1 1 0 1 1 0 1 1
"B" 0 0 0 1 1 1 0 1 . .
"C" 0 1 1 1 1 1 0 1 1 0
"D" 0 1 1 1 1 0 0 0 1 0
end

// SAVE A COPY OF THE ORIGINAL DATA
tempfile copy
save `copy'


// GO LONG
reshape long m, i(id) j(_j)
// IDENTIFY SPELLS OF CONSECUTIVE REPEATS AND CALCULATE LENGTH
by id (_j), sort: gen spell = sum(m != m[_n-1])
by id spell (_j), sort: gen c = _N
// PRESERVE ONLY SPELLS OF ONES
drop if m == 0 | missing(m)
// GO TO ONE OBSERVATION PER SPELL
by id spell: keep if _n == 1
// NUMBER THEM SEQUENTIALLY
by id: replace _j = _n
// CLEAN UP AND RETURN TO WIDE
drop spell m
reshape wide c, i(id) j(_j)
merge 1:m id using `copy'

Thanks a lot, Clyde. The code worked perfectly.