Panel data: how to calculate date difference in long panel data

[Roshani Patel]

Dear Stata community,

Many thanks for all your help so far. Firstly I have found these forums so useful for my work.... I hope that I can contribute eventually (but for now I am still at a novice stage).

I have long panel data for multiple participant IDs. I would like to work out the number of days (or months) between each exam date (date_exam) for each participant (id). Time Zero is the first date for each participant (id).

My data is as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(observation id) float(date_exam date_diff advanced_cuff)
 3 1 15753 0 1
 4 1 16117 . 0
 5 1 16481 . 2
 6 1 16519 . .
 7 1 16705 . 1
 8 1 17050 . 1
 9 1 17090 . .
10 1 17286 . 1
11 1 17454 . 2
12 1 17636 . 1
13 1 17825 . .
14 1 18189 . 1
15 1 18546 . 1
16 1 18910 . 2
17 1 19099 . 2
18 1 19540 . 0
19 1 19988 . 1
20 1 20849 . .
21 1 21327 . 1
22 1 21622 . 2
24 3 14321 0 1
25 3 15263 . 0
26 3 15766 . 0
27 3 16362 . 0
28 3 17100 . 0
29 3 17461 . 0
30 3 17827 . 0
31 3 18191 . 1
32 3 18968 . 1
33 3 19472 . 0
34 3 20298 . 0
35 3 20886 . 2
37 3 21142 . 2
38 3 21369 . 2
44 4 20828 0 1
45 4 21314 . 1
47 5 17023 0 1
48 5 17349 . 0
49 5 17720 . 2
50 5 18084 . 2
end
format %tdDD/NN/CCYY date_exam

I've tried searching the internet and stata youtube.... but can't find any codes/ formulas specific to my queries.

Very many thanks once again,

Best wishes,

Roshani

[Carlo Lazzaro]

Roshani:
do you mean something along the following lines?

. bysort id: gen date_req= date_exam[_n]-date_exam[_n-1]
(4 missing values generated)

. list if id==1

     +-------------------------------------------------------------+
     | observ~n   id    date_exam   date_d~f   advanc~f   date_req |
     |-------------------------------------------------------------|
  1. |        3    1   17/02/2003          0          1          . |
  2. |        4    1   16/02/2004          .          0        364 |
  3. |        5    1   14/02/2005          .          2        364 |
  4. |        6    1   24/03/2005          .          .         38 |
  5. |        7    1   26/09/2005          .          1        186 |
     |-------------------------------------------------------------|
  6. |        8    1   06/09/2006          .          1        345 |
  7. |        9    1   16/10/2006          .          .         40 |
  8. |       10    1   30/04/2007          .          1        196 |
  9. |       11    1   15/10/2007          .          2        168 |
 10. |       12    1   14/04/2008          .          1        182 |
     |-------------------------------------------------------------|
 11. |       13    1   20/10/2008          .          .        189 |
 12. |       14    1   19/10/2009          .          1        364 |
 13. |       15    1   11/10/2010          .          1        357 |
 14. |       16    1   10/10/2011          .          2        364 |
 15. |       17    1   16/04/2012          .          2        189 |
     |-------------------------------------------------------------|
 16. |       18    1   01/07/2013          .          0        441 |
 17. |       19    1   22/09/2014          .          1        448 |
 18. |       20    1   30/01/2017          .          .        861 |
 19. |       21    1   23/05/2018          .          1        478 |
 20. |       22    1   14/03/2019          .          2        295 |
     +-------------------------------------------------------------+

.

[Roshani Patel]

Dear Carlo, Thank you for this. This is not exactly what I was after (but this will be useful for some other work I am doing) However What I would like is for the first date point for each id to be time zero and the exams thereafter to be calculated from the first date entry point. Does my query make sense? Many thanks again

[River Huang]

Dear Roshani, Is this what you wanted?

bys id (date_exam): egen tem = max((date_diff == 0)*date_exam)
gen wanted = date_exam-tem

[Nick Cox]

bysort id (date_exam) : gen wanted = date_exam - date_exam[1]

[Roshani Patel]

Dear Both,

Many thanks this.

Nick, I have tried your formula and it is exactly what I wanted. Many thanks again.

Roshani