Serial, overlapping and nested hospital transfers

[Belaynew Taye]

Hi Clyde
I would like to have data that does not have transfers in it and compute length of stay and other times. I want to create a transfer sequence variable for that.
I have dat that has with in hospital transfer which I want to merge the duration (overlapping and serial transfers) and have one date of admission and date of discharge instead. I also want to remove nested transfers from the data as they can be cases going to different units in the hospital but reported as separate cases. Are there commands around?

ID	date of admission	date of discharge
1	30-Jan-13	10-Feb-13
1	3-Feb-13	6-Feb-13
2	14-Jan-14	15-Jan-14
2	15-Jan-14	5-Feb-14
3	17-Sep-15	20-Sep-15
3	18-Sep-15	23-Sep-15

patient 1 has a nested transfer within the admission which I want to remove from the data.
Patient 2 has serial transfer and I want to merge with previous admission, with the new dates being 14-Jan-14(date of admission) to 5-Feb-14 (new date of discharge)
Patient 3 has overlapping transfer and I want to merge with previous admission, with new dates being 17-Sep-15 (date of admission) to 23-Sep-2015(date of discharge)
Appreciate any help with this.

[Andrew Musau]

Please familiarize yourself with the dataex command to provide data examples (see FAQ Advice #12 for details). I assume that your admission and discharge variables are proper Stata date variables and not strings.

clear
input byte id float(admission discharge)
1 19388 19399
1 19392 19395
2 19737 19738
2 19738 19759
3 20348 20351
3 20349 20354
end
format %tdD-N-Y admission
format %tdD-N-Y discharge

bys id (admission): gen seq=_n
bys id (admission): replace seq=seq[_n-1] if admission<= discharge[_n-1] &_n>1
bys id seq (discharge): replace discharge= discharge[_N]
bys id seq (admission): keep if _n==1

Res.:

. l

     +--------------------------------+
     | id   admiss~n   discha~e   seq |
     |--------------------------------|
  1. |  1   30-01-13   10-02-13     1 |
  2. |  2   14-01-14   05-02-14     1 |
  3. |  3   17-09-15   23-09-15     1 |
     +--------------------------------+

.

[Belaynew Taye]

That works great. Thanks a lot Andrew

[Clyde Schechter]

Actually, there are some cases that the code in #2 will not handle correctly. Here's a quick demonstration of such a case, formed by adding a third nested admission to id 1's data:

. clear

. input byte id float(admission discharge)

           id  admission  discharge
  1. 1 19388 19399
  2. 1 19392 19395
  3. 1 19396 19398
  4. 2 19737 19738
  5. 2 19738 19759
  6. 3 20348 20351
  7. 3 20349 20354
  8. end

. format %tdD-N-Y admission

. format %tdD-N-Y discharge

.
. list if id == 1

     +--------------------------+
     | id   admiss~n   discha~e |
     |--------------------------|
  1. |  1   30-01-13   10-02-13 |
  2. |  1   03-02-13   06-02-13 |
  3. |  1   07-02-13   09-02-13 |
     +--------------------------+

.
. bys id (admission): gen seq=_n

. bys id (admission): replace seq=seq[_n-1] if admission<= discharge[_n-1] &_n>1
(3 real changes made)

. bys id seq (discharge): replace discharge= discharge[_N]
(3 real changes made)

. bys id seq (admission): keep if _n==1
(3 observations deleted)

.
. list if id == 1

     +--------------------------------+
     | id   admiss~n   discha~e   seq |
     |--------------------------------|
  1. |  1   30-01-13   10-02-13     1 |
  2. |  1   07-02-13   09-02-13     3 |
     +--------------------------------+

Those three admissions should reduce to a single admission as the second and third are nested in the first.

Here's a different approach that handles all possible contingencies:

clear
input byte id float(admission discharge)
1 19388 19399
1 19392 19395
1 19396 19398
2 19737 19738
2 19738 19759
3 20348 20351
3 20349 20354
end
format %tdD-N-Y admission
format %tdD-N-Y discharge

list if id == 1

rename (admission discharge) date=
gen `c(obs_t)' obs_no = _n
reshape long date, i(obs_no) j(event) string
by id (date event), sort: gen int state = sum((event == "admission") - (event == "discharge"))
by id (date event): gen seq = sum(inlist(state[_n-1], 0, .))
collapse (min) admission = date (max) discharge = date, by(id seq)

list if id == 1

[Belaynew Taye]

Thanks Clyde. It does solve to take out some of the embedded ones that remain after I keep obs with seq==1 using the codes from Andrew.
Here is an example that remains not deleted from Andrew's approach:

(**###sorry for format needs here*###)



id admission discharge
1 15-06-2016 15-06-2016
1 17-06-2016 17-06-2016
1 15-06-2016 27-06-2016

Andrew's code provides me with:

id admission discharge seq
1 15-06-2016 27-06-2016 1
1 17-06-2016 17-06-2016 3


I am thinking the admission date for the second row was > discharge date for the first(previous) row and left it out.
From what I have started working with, Andrew's code with more handling mechanisms would be so great.
I appreciate your support.

[Belaynew Taye]

Working on further, I added another round of run to replace and remove transfers let out by the first run from Andrew's code, and that clears all the transfers!

bys id (admission):replace seq=seq[_n-1] if admission <=discharge[_n-1]&_n>1
bys id seq( discharge): replace discharge=discharge[_N]
bys if seq(admission):keep if _n==1