Computing a cumulative score

[Danielle leblanc]

Hi,

I have the following sample of data. I have an indicator of 1 and 0 in once column and I want to compute the sum of consecutive 1s (i.e the cumulative column) for each ID . . How can I do that in stata?

[Justin Niakamal]

You can do this using Nick Cox's tsspell (ssc)

clear
input byte id int year byte(score cumulative)
1 2001 0 0
1 2002 1 1
1 2003 0 0
1 2004 1 1
1 2005 1 2
2 2001 1 1
2 2002 1 2
2 2003 1 3
3 2001 0 0
3 2002 1 1
3 2003 1 2
3 2004 0 0
3 2005 1 1
3 2006 1 2
end



tsset id year 
tsspell score
bys id  _spell (year): gen wanted = sum(score)

assert wanted == cumulative

[Nick Cox]

Justin Niakamal is right. Here is another way to do it.

clear
input byte id int year byte(score cumulative)
1 2001 0 0
1 2002 1 1
1 2003 0 0
1 2004 1 1
1 2005 1 2
2 2001 1 1
2 2002 1 2
2 2003 1 3
3 2001 0 0
3 2002 1 1
3 2003 1 2
3 2004 0 0
3 2005 1 1
3 2006 1 2
end

bysort id : gen wanted = score if score == 1 & inlist(score[_n-1], ., 0)
by id: replace wanted = wanted[_n-1] + score if wanted == . & score == 1
replace wanted = 0 if wanted == .

list, sepby(id)
    +---------------------------------------+
     | id   year   score   cumula~e   wanted |
     |---------------------------------------|
  1. |  1   2001       0          0        0 |
  2. |  1   2002       1          1        1 |
  3. |  1   2003       0          0        0 |
  4. |  1   2004       1          1        1 |
  5. |  1   2005       1          2        2 |
     |---------------------------------------|
  6. |  2   2001       1          1        1 |
  7. |  2   2002       1          2        2 |
  8. |  2   2003       1          3        3 |
     |---------------------------------------|
  9. |  3   2001       0          0        0 |
 10. |  3   2002       1          1        1 |
 11. |  3   2003       1          2        2 |
 12. |  3   2004       0          0        0 |
 13. |  3   2005       1          1        1 |
 14. |  3   2006       1          2        2 |
     +---------------------------------------+

As recently advised, please do not use screenshots and do use dataex. https://www.statalist.org/forums/for...tted-n-xtivreg