Sum Variable in colums

[Patrick Turcotte]

Hello i have

id	Year	temp	"seq"
1	2001	.	0
1	2002	1	1
1	2003	1	2
1	2004	.	0
2	2001	1	1
2	2002	1	2
2	2003	.	0

I need to find a way to calculate the var "seq"

I try

sort id
bys id: egen seq = anymatch(temp), value (1)

Not giving the result I want

I need to add temp value by id and year, and put info into a new var "seq".

Thanks

[David Benson]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id int year byte(temp seq)
1 2001 . 0
1 2002 1 1
1 2003 1 2
1 2004 . 0
2 2001 1 1
2 2002 1 2
2 2003 . 0
end

sort id year
bysort id (year): gen seq2 = sum( temp)
replace seq2 = 0 if temp==.

. list id year temp seq*, sepby(id) noobs

  +-------------------------------+
  | id   year   temp   seq   seq2 |
  |-------------------------------|
  |  1   2001      .     0      0 |
  |  1   2002      1     1      1 |
  |  1   2003      1     2      2 |
  |  1   2004      .     0      0 |
  |-------------------------------|
  |  2   2001      1     1      1 |
  |  2   2002      1     2      2 |
  |  2   2003      .     0      0 |
  +-------------------------------+

[Patrick Turcotte]

Thank M. Benson,

I'm very near

id year seq result i need
1 2000 0 0
1 2001 1 1
1 2002 1 2
1 2003 0 0
1 2004 1 1
1 2005 1 2
1 2006 1 3
1 2007 1 4
1 2008 1 5
1 2009 0 0
.....

everytime I have a missing value or a 0, I have to restart my count, see in red for the result I search

Thank

[Patrick Turcotte]

Thanks to all, with try and error I find the solution:

sort id year
bys id (year): gen seq = sum(temp)
bys id (year) : replace seq = cond(missing(temp[_n-1]), temp, temp + seq[_n-1])

[Nick Cox]

This is more difficult to follow than it need be.

In #1 temp is the input and seq counts the number of 1s in each separate run or spell of temp. .

In #3 seq is the input and result_I_need is the counter.

In #4 we are back to the names in #1.

Please use dataex for data examples. https://www.statalist.org/forums/help#stata explains.

If the rules are that counting is separate for each spell of 1s, then the code in #5 fails if there is more than one spell for each identifier.

Some principles of working with spells are discussed in detail in https://www.stata-journal.com/sjpdf....iclenum=dm0029

Separately from that, tsspell (SSC) is a convenience tool implementing those principles. Here is an extended example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id int year byte(seq needed)
1 2000 0 0
1 2001 1 1
1 2002 1 2
1 2003 0 0
1 2004 1 1
1 2005 1 2
1 2006 1 3
1 2007 1 4
1 2008 1 5
1 2009 0 0
1 2010 1 1
1 2011 . 0
1 2012 1 1
1 2013 1 2
1 2014 1 3
1 2015 1 4
1 2016 1 5
end

. tsset id year
       panel variable:  id (strongly balanced)
        time variable:  year, 2000 to 2016
                delta:  1 unit

. tsspell, cond(seq == 1)

. list, sepby(id _spell)

     +-------------------------------------------------+
     | id   year   seq   needed   _seq   _spell   _end |
     |-------------------------------------------------|
  1. |  1   2000     0        0      0        0      0 |
     |-------------------------------------------------|
  2. |  1   2001     1        1      1        1      0 |
  3. |  1   2002     1        2      2        1      1 |
     |-------------------------------------------------|
  4. |  1   2003     0        0      0        0      0 |
     |-------------------------------------------------|
  5. |  1   2004     1        1      1        2      0 |
  6. |  1   2005     1        2      2        2      0 |
  7. |  1   2006     1        3      3        2      0 |
  8. |  1   2007     1        4      4        2      0 |
  9. |  1   2008     1        5      5        2      1 |
     |-------------------------------------------------|
 10. |  1   2009     0        0      0        0      0 |
     |-------------------------------------------------|
 11. |  1   2010     1        1      1        3      1 |
     |-------------------------------------------------|
 12. |  1   2011     .        0      0        0      0 |
     |-------------------------------------------------|
 13. |  1   2012     1        1      1        4      0 |
 14. |  1   2013     1        2      2        4      0 |
 15. |  1   2014     1        3      3        4      0 |
 16. |  1   2015     1        4      4        4      0 |
 17. |  1   2016     1        5      5        4      1 |
     +-------------------------------------------------+

For those who prefer to use official code and use community-contributed commands only when obliged, here is another way to do it:

bysort id (year) : gen autre = seq == 1 & seq[_n-1] != 1
by id: replace autre = autre[_n-1] + 1 if autre == 0 & seq == 1

[Patrick Turcotte]

Thanks Mr Cox