Generating count based on missing values

[Ahmed Khan]

Hi All, I am having a problem in generating the COUNT of missing values. Please see the below table. In the below table, I have the "HAVE" column, and I want a stata command to generate the"WANT" column. In the "WANT" column, I want to count the missing values from "HAVE", however, the count in the "WANT" column should always reset when there is a "5555555" value in the "HAVE" column. I hope that I have explained my problem.

any help would be much appreciated.

HAVE	WANT
.	1
.	2
5555555	5555555
.	1
.	2
.	3
5555555	5555555
5555555	5555555
.	1
.	2
.	3
.	4
5555555	5555555

[David Benson]

This code is based on ideas from: cumulative sum that restarts at 0, if observe "0" in the original column.

gen want = have
replace want = 1 if _n==1
replace want = 1 if want[_n-1]==5555555 & want==.
replace want = want[_n-1] + 1 if want==.


* NOTE: I just manually entered id so that I could make this list look good using sepby(id)
. list id have want, sepby(id)

     +------------------------+
     | id      have      want |
     |------------------------|
  1. |  1         .         1 |
  2. |  1         .         2 |
     |------------------------|
  3. |  2   5555555   5555555 |
  4. |  2         .         1 |
  5. |  2         .         2 |
  6. |  2         .         3 |
     |------------------------|
  7. |  3   5555555   5555555 |
  8. |  3   5555555   5555555 |
  9. |  3         .         1 |
 10. |  3         .         2 |
 11. |  3         .         3 |
 12. |  3         .         4 |
     |------------------------|
 13. |  4   5555555   5555555 |
 14. |  4         .         1 |
 15. |  4         .         2 |
 16. |  4         .         3 |
     +------------------------+

[Ahmed Khan]

Thanks alot David. This worked perfectly. Much Appreciated.

[Nick Cox]

david Benson's code is fine. But other takes are possible. One is that you are identifying spells of missing values. You may well have a time or other sequence variable. Even if not you can always create one.

tsspell from SSC, which must be installed before you can use it, gives a handle on spells.

clear 
input HAVE    WANT
.    1
.    2
5555555    5555555
.    1
.    2
.    3
5555555    5555555
5555555    5555555
.    1
.    2
.    3
.    4
5555555    5555555
end 

gen time = _n 
tsset time 

tsspell , cond(missing(HAVE))  

replace _seq = HAVE if _seq == 0 

list, sepby(_spell) 

     +----------------------------------------------------+
     |    HAVE      WANT   time      _seq   _spell   _end |
     |----------------------------------------------------|
  1. |       .         1      1         1        1      0 |
  2. |       .         2      2         2        1      1 |
     |----------------------------------------------------|
  3. | 5555555   5555555      3   5555555        0      0 |
     |----------------------------------------------------|
  4. |       .         1      4         1        2      0 |
  5. |       .         2      5         2        2      0 |
  6. |       .         3      6         3        2      1 |
     |----------------------------------------------------|
  7. | 5555555   5555555      7   5555555        0      0 |
  8. | 5555555   5555555      8   5555555        0      0 |
     |----------------------------------------------------|
  9. |       .         1      9         1        3      0 |
 10. |       .         2     10         2        3      0 |
 11. |       .         3     11         3        3      0 |
 12. |       .         4     12         4        3      1 |
     |----------------------------------------------------|
 13. | 5555555   5555555     13   5555555        0      0 |
     +----------------------------------------------------+

[Ahmed Khan]

Thank you Nick for your help. Much appreciated.