Creating a sequence. Where is the error in my command

[Don Spady]

I am trying to create a sequence that indicates when a value in another variable is not missing. The commands I am using are: sort id dovc
by id:egen maxstop = max(seqstop)
by id:egen maxstart = max(seqstart)
order seqcens seqstart seqstop maxstop maxstart, after(dstp1cens)

A sample of the data (and the results) are below:

| id dovc visnum dstt1 dstp1 seqstart seqstop |
|----------------------------------------------------------------------|
112. | 16 22may2002 1 . . . . |
113. | 16 17may2004 2 01may2002 09mar2012 1 . |
114. | 16 19may2005 3 . 09mar2012 . . |
115. | 16 27may2011 4 . 09mar2012 . . |
116. | 16 22mar2012 5 . 09mar2012 . 1 |
|----------------------------------------------------------------------|
117. | 16 15jul2013 6 16mar2012 . 2 . |
429. | 54 28may2003 1 . . . . |
430. | 54 18may2004 2 . . . . |
431. | 54 20oct2004 3 . . . . |
432. | 54 11feb2005 4 . . . . |
|----------------------------------------------------------------------|
433. | 54 16jun2005 5 . . . . |
434. | 54 20jan2006 6 18aug2005 06mar2013 1 . |
435. | 54 04may2006 7 . 06mar2013 . . |
436. | 54 23nov2006 8 18aug2005 06mar2013 2 . |
437. | 54 23may2007 9 18aug2005 06mar2013 3 . |
|----------------------------------------------------------------------|
438. | 54 23jun2008 10 . 06mar2013 . . |
439. | 54 24aug2010 11 . 06mar2013 . 1 |
440. | 54 04apr2013 12 03may2013 . 4 . |
441. | 54 06jun2014 13 . . . . |
684. | 76 21jul2003 1 . . . . |
|----------------------------------------------------------------------|
685. | 76 02jun2004 2 . . . . |
686. | 76 08jun2005 3 . . . . |
687. | 76 20jul2006 4 . . . . |
688. | 76 05oct2010 5 . . . . |
689. | 76 16mar2011 6 . 10aug2011 . . |
|----------------------------------------------------------------------|
690. | 76 12jan2012 7 06jan2011 10aug2011 1 1 |
691. | 76 23oct2012 8 08jun2012 . 2 . |
+----------------------------------------------------------------------+


My question is why if dstp1 is not missing, is there no value for seqstop. e.g. line 113 should have seqstop == 1; line 114 seqstop ==2 and so on for the id. However, it is only the last line where there is a dstp1 value that seqstop takes a value. On the other hand, seqstart seems to be working OK.

What am I doing wrong.
Many thanks.
Don

[Joe Canner]

Don,

I think your post is missing something. The code you present creates maxstart and maxstop from seqstart and seqstop, but your question seems to pertain to how to create seqstart and seqstop from dstt1 and dstp1. Can you check and make sure we have all of the information we need?

Regards,
Joe

[Don Spady]

My apologies. You are right. Stupid mistake.

Here is the code sort id dovc dstp1
by id:egen seqstop = seq() if dstp1 !=.
sort id dovc dstt1
by id: egen seqstart = seq() if dstt1 !=.

Don

[Nick Cox]

It's a little difficult to work out what you want.

Please see FAQ Advice on advice on using CODE delimiters and on providing data examples in a form we can easily copy for play.

The first part of the code below focuses on replicating your data sandbox.

 
clear 
input id str9 dovc visnum str9 dstt1 str9 dstp1 seqstart seqstop
16 "22may2002" 1 "." "." . .
16 "17may2004" 2 "01may2002" "09mar2012" 1 .
16 "19may2005" 3 "." "09mar2012" . .
16 "27may2011" 4 "." "09mar2012" . .
16 "22mar2012" 5 "." "09mar2012" . 1
16 "15jul2013" 6 "16mar2012" "." 2 .
54 "28may2003" 1 "." "." . .
54 "18may2004" 2 "." "." . .
54 "20oct2004" 3 "." "." . .
54 "11feb2005" 4 "." "." . .
54 "16jun2005" 5 "." "." . .
54 "20jan2006" 6 "18aug2005" "06mar2013" 1 .
54 "04may2006" 7 "." "06mar2013" . .
54 "23nov2006" 8 "18aug2005" "06mar2013" 2 .
54 "23may2007" 9 "18aug2005" "06mar2013" 3 .
54 "23jun2008" 10 "." "06mar2013" . .
54 "24aug2010" 11 "." "06mar2013" . 1
54 "04apr2013" 12 "03may2013" "." 4 .
54 "06jun2014" 13 "." "." . .
76 "21jul2003" 1 "." "." . .
76 "02jun2004" 2 "." "." . .
76 "08jun2005" 3 "." "." . .
76 "20jul2006" 4 "." "." . .
76 "05oct2010" 5 "." "." . .
76 "16mar2011" 6 "." "10aug2011" . .
76 "12jan2012" 7 "06jan2011" "10aug2011" 1 1
76 "23oct2012" 8 "08jun2012" "." 2 .
end 

foreach v in dovc dstt1 dstp1 { 
    gen work = daily(`v', "DMY") 
    drop `v' 
    rename work `v' 
    format `v' %td 
}

I wouldn't use seq() here, fond though I am of it as its parent, but try something more direct:

bysort id (dovc dstt1) : gen seq_t = cond(dstt1 < ., sum(dstt1 < .), .) 
bysort id (dovc dstp1) : gen seq_p = cond(dstp1 < ., sum(dstp1 < .), .) 

sort id visnum 
list id visnum ds* seq_*, sepby(id)

Here are the results:

 
     +-----------------------------------------------------+
     | id   visnum       dstt1       dstp1   seq_t   seq_p |
     |-----------------------------------------------------|
  1. | 16        1           .           .       .       . |
  2. | 16        2   01may2002   09mar2012       1       1 |
  3. | 16        3           .   09mar2012       .       2 |
  4. | 16        4           .   09mar2012       .       3 |
  5. | 16        5           .   09mar2012       .       4 |
  6. | 16        6   16mar2012           .       2       . |
     |-----------------------------------------------------|
  7. | 54        1           .           .       .       . |
  8. | 54        2           .           .       .       . |
  9. | 54        3           .           .       .       . |
 10. | 54        4           .           .       .       . |
 11. | 54        5           .           .       .       . |
 12. | 54        6   18aug2005   06mar2013       1       1 |
 13. | 54        7           .   06mar2013       .       2 |
 14. | 54        8   18aug2005   06mar2013       2       3 |
 15. | 54        9   18aug2005   06mar2013       3       4 |
 16. | 54       10           .   06mar2013       .       5 |
 17. | 54       11           .   06mar2013       .       6 |
 18. | 54       12   03may2013           .       4       . |
 19. | 54       13           .           .       .       . |
     |-----------------------------------------------------|
 20. | 76        1           .           .       .       . |
 21. | 76        2           .           .       .       . |
 22. | 76        3           .           .       .       . |
 23. | 76        4           .           .       .       . |
 24. | 76        5           .           .       .       . |
 25. | 76        6           .   10aug2011       .       1 |
 26. | 76        7   06jan2011   10aug2011       1       2 |
 27. | 76        8   08jun2012           .       2       . |
     +-----------------------------------------------------+

Does that help?

[Don Spady]

Yes, that is just what I want.
Many thanks
Please accept my apologies for not putting the data in the right format for use. I thought I had.
Don

[Nick Cox]

We've clarified this only just recently: http://www.statalist.org/forums/help Section 12.

The criterion is: can a reader copy, paste and go with what you posted. (Dates are nasty to copy and paste because as displayed they usually show a date format.)