Re: st: Create Timeline based on Dates

[Nick Cox <njcoxstata@gmail.com>]

For problems like this I need a sandpit to play in. Here is one I made:

list, sepby(id)

     +-------------------+
     | id   date   event |
     |-------------------|
  1. |  1     13       0 |
  2. |  1     13       0 |
  3. |  1     14       1 |
  4. |  1     15       0 |
  5. |  1     17       0 |
  6. |  1     18       1 |
  7. |  1     19       0 |
     |-------------------|
  8. |  2     14       0 |
  9. |  2     15       0 |
 10. |  2     15       1 |
 11. |  2     17       0 |
 12. |  2     18       0 |
 13. |  2     19       1 |
 14. |  2     20       0 |
     +-------------------+

I see this as follows.

1. There is a date looking forward, which is (present date - previous
event date), and is thus zero or positive

1'. There is a twist on 1: There can be multiple observations with the
same date.

2. There is a date looking backward which is (present date - next
event date), and is thus zero or negative

2'. As 1'.

3. The wanted date is the smaller in absolute value. If there is a tie
in absolute value, I choose the positive value.

4. For dates before the first event, no previous date can be
identified. But this is not a problem, as the backward date will be
the solution for these dates.

4. For dates after the last event, no next date can be identified. But
this is not a problem, as the forward date will be the solution for
these dates.

To get "forward dates", we just copy previous values as needed, after
spreading each event to all dates that are the same:

gen prev = date if event == 1
bysort id date (prev) : replace prev = prev[1] if prev[1] == 1
bysort id (date) : replace prev = prev[_n-1] if mi(prev)
gen forward = date - prev

To get "backward dates", we can use the trick of reversing time.

gen negdate = -date
gen next = date if event == 1
bysort id negdate (next) : replace next = next[1] if next[1] == 1
bysort id (negdate) : replace next = next[_n-1] if mi(next)
gen backward = date - next

Now can we do the comparison:
.
gen timeline = cond(abs(forward) <= abs(backward), forward, backward)
sort id date

list id date forw backw timeline, sepby(id)

     +-------------------------------------------+
     | id   date   forward   backward   timeline |
     |-------------------------------------------|
  1. |  1     13         .         -1         -1 |
  2. |  1     13         .         -1         -1 |
  3. |  1     14         0          0          0 |
  4. |  1     15         1         -3          1 |
  5. |  1     17         3         -1         -1 |
  6. |  1     18         0          0          0 |
  7. |  1     19         1          .          1 |
     |-------------------------------------------|
  8. |  2     14         .         -1         -1 |
  9. |  2     15         0          0          0 |
 10. |  2     15         0          0          0 |
 11. |  2     17         2         -2          2 |
 12. |  2     18         3         -1         -1 |
 13. |  2     19         0          0          0 |
 14. |  2     20         1          .          1 |
     +-------------------------------------------+

For the complication with weekends, Stata offers business calendars as
a complete solution. I have never used them.

On Fri, Oct 26, 2012 at 1:02 AM, Lisa Wang <lhwang0925@gmail.com> wrote:

> I would like to create a timeline based on some event date (ie. ...-5,
> -4, -3, -2, -1, 0, +1, +2, +3...etc). I have different students names
> in a variable named "as" (column 1)  and also a set of dates (column
> 2) as well as another variable 'edate' (column 3) which has the event
> dates and . everywhere else if it didn't match with column 2. What I
> would like to know is how to create the timeline with the event date
> being 0 for each student.
>
> This is the code I have run so far:
>
> - bysort as: generate rank =_n
>
> . bysort as: generate erank = rank if date==edate
>
> . bysort as: egen erank_pop = min(erank)
>
> . bysort as: generate t = rank -erank_pop -
>
> There are three problems which have me now stuck.
>
> 1. I might have multiple observations for a particular student on the
> same date as well. Therefore, when I run the first line of code, it's
> already erroneous as Stata will treat it as being different dates. I
> tried also -bysort as(date): generate rank =_n - instead but it
> returns an error: "factor variables and time-series operators not
> allowed".
>
> 2. Sometimes I have multiple event dates for a particular student - I
> would like Stata to guess which event date the date is closer to and
> then do the time differences from that.
>
> 3. The dates in column 2 have all weekdays but no weekends (as the
> students don't need to go to school on those days), so if I do a
> timeline then it will skip some dates (eg. -5 then to -2,-1 etc. as a
> result of the weekend). How would I overcome this, so that it actually
> is -3,-2,-1 etc?
>
>
> Thank you so much. My email is a bit long but just wanted everyone to
> understand what I wanted to achieve.
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/