generate variable time to event. Several measures in time.

[Augusto Sao]

Please, I need to create a variable event and time to event. But I have different measures. The variable is pcrresult and its value is 1


----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input int id float datepcrrequest2 byte pcrresult2 float datepcrrequest3 byte pcrresult3 float datepcrrequest4 byte pcrresult4 float datepcrrequest5 byte pcrresult5
 1 20265 2     . .     . .     . .
 2 19785 2 20996 2     . .     . .
 3 18379 2 18393 2     . .     . .
 4 18582 2 19841 2     . .     . .
 5 19802 2 20170 2 20520 2 20730 2
 6 19926 2 20306 2 20488 2 20801 2
 7 18624 2 18805 2     . .     . .
 8 18568 2 18833 2 19183 2 19456 2
 9 20247 2 20394 2 20772 2     . .
10 20076 2 20436 2 20773 2     . .
11 20044 2 20401 2 20765 2 21182 2
12 19988 2     . .     . .     . .
13 18543 2 18550 2 18564 2 18578 2
14 19799 2 19981 2     . .     . .
15 20202 2 20384 2 20906 2 21252 2
end
format %tdDD/NN/CCYY datepcrrequest2
format %tdDD/NN/CCYY datepcrrequest3
format %tdDD/NN/CCYY datepcrrequest4
format %tdDD/NN/CCYY datepcrrequest5

------------------ copy up to and including the previous line ------------------

Listed 15 out of 389 observations

.

[David Benson]

Hi Augusto, thanks for sharing your data using dataex!

Below I've reshaped your data from wide to long because you will probably need to do that to calculate any sort of "time since event" data. I didn't see any values where pcr_result==1. However, below I calculate time-between-events variables in hopes that you help you move forward.

reshape long datepcrrequest pcrresult, i(id) j(n)  // help reshape
rename datepcrrequest date_pcr_request  // just renaming to make easier to read
rename pcrresult pcr_result

. list if id<=5, sepby(id) abbrev(14)

     +--------------------------------------+
     | id   n   date_pcr_req~t   pcr_result |
     |--------------------------------------|
  1. |  1   2       26/06/2015            2 |
  2. |  1   3                .            . |
  3. |  1   4                .            . |
  4. |  1   5                .            . |
     |--------------------------------------|
  5. |  2   2       03/03/2014            2 |
  6. |  2   3       26/06/2017            2 |
  7. |  2   4                .            . |
  8. |  2   5                .            . |
     |--------------------------------------|
  9. |  3   2       27/04/2010            2 |
 10. |  3   3       11/05/2010            2 |
 11. |  3   4                .            . |
 12. |  3   5                .            . |
     |--------------------------------------|
 13. |  4   2       16/11/2010            2 |
 14. |  4   3       28/04/2014            2 |
 15. |  4   4                .            . |
 16. |  4   5                .            . |
     |--------------------------------------|
 17. |  5   2       20/03/2014            2 |
 18. |  5   3       23/03/2015            2 |
 19. |  5   4       07/03/2016            2 |
 20. |  5   5       03/10/2016            2 |
     +--------------------------------------+

bysort id (n): gen day_diff = date_pcr_request - date_pcr_request[_n-1]  // days since last observation
egen first_date = min(date_pcr_request), by(id)
egen  last_date = max(date_pcr_request), by(id)  // this will have to get updated every time new data is added to dataset
drop if date_pcr_request==. & pcr_result==.
gen days_since_first = date_pcr_request - first_date  // days since earliest pcr request
bysort id (n): gen count = _N  // how many times is the individual in the data
order count, after(n)  // just moving variables around
order days_since_first, after( day_diff)
format first_date last_date %td
format day_diff days_since_first %10.0gc

. list if id<=7, sepby(id) abbrev(16) noobs

  +-------------------------------------------------------------------------------------------------------+
  | id   n   count   date_pcr_request   pcr_result   day_diff   days_since_first   first_date   last_date |
  |-------------------------------------------------------------------------------------------------------|
  |  1   2       1         26/06/2015            2          .                  0    26jun2015   26jun2015 |
  |-------------------------------------------------------------------------------------------------------|
  |  2   2       2         03/03/2014            2          .                  0    03mar2014   26jun2017 |
  |  2   3       2         26/06/2017            2      1,211              1,211    03mar2014   26jun2017 |
  |-------------------------------------------------------------------------------------------------------|
  |  3   2       2         27/04/2010            2          .                  0    27apr2010   11may2010 |
  |  3   3       2         11/05/2010            2         14                 14    27apr2010   11may2010 |
  |-------------------------------------------------------------------------------------------------------|
  |  4   2       2         16/11/2010            2          .                  0    16nov2010   28apr2014 |
  |  4   3       2         28/04/2014            2      1,259              1,259    16nov2010   28apr2014 |
  |-------------------------------------------------------------------------------------------------------|
  |  5   2       4         20/03/2014            2          .                  0    20mar2014   03oct2016 |
  |  5   3       4         23/03/2015            2        368                368    20mar2014   03oct2016 |
  |  5   4       4         07/03/2016            2        350                718    20mar2014   03oct2016 |
  |  5   5       4         03/10/2016            2        210                928    20mar2014   03oct2016 |
  |-------------------------------------------------------------------------------------------------------|
  |  6   2       4         22/07/2014            2          .                  0    22jul2014   13dec2016 |
  |  6   3       4         06/08/2015            2        380                380    22jul2014   13dec2016 |
  |  6   4       4         04/02/2016            2        182                562    22jul2014   13dec2016 |
  |  6   5       4         13/12/2016            2        313                875    22jul2014   13dec2016 |
  |-------------------------------------------------------------------------------------------------------|
  |  7   2       2         28/12/2010            2          .                  0    28dec2010   27jun2011 |
  |  7   3       2         27/06/2011            2        181                181    28dec2010   27jun2011 |
  +-------------------------------------------------------------------------------------------------------+

[Augusto Sao]

Dear David, I am eternally grateful for your help. There is some way to do it without taking it to the long format ?