I would like to find out the hospital transitions of each patient during year 2018. The claims data look like below (1) The data I have looks like: PatID FacID clm_from_dt clm_thru_dt 001 40001 1/3/2018 1/6/2018 001 40001 1/14/2018 1/19/2018 001 40001 3/7/2018 3/10/2018 The data I want looks like (need to insert rows to make up the date gap between two claims in order to capture the transition): PatID FacID clm_from_dt clm_thru_dt 001 40001 1/3/2018 1/6/2018 001 1/6/2018 1/14/2018 001 40001 1/14/2018 1/19/2018 001 1/19/2018 3/7/2018 001 40001 3/7/2018 3/10/2018 001 3/10/2018 12/31/2018 (2) For some patients, they visited hospital multiple times during their stay in a long term care facility. So there is overlapping between the previous claim through date and next claim from date. The data looks like below: PatID FacID clm_from_dt clm_thru_dt 002 43517 2/1/2018 3/31/2018 002 40002 2/10/2018 2/11/2018 002 42168 2/12/2018 2/22/2018 002 43517 4/1/2018 5/31/2018 002 40002 4/5/2018 4/7/2018 002 43517 6/1/2018 7/31/2018 For this situation, I would like the data looks like below: PatID FacID clm_from_dt clm_thru_dt 002 43517 2/1/2018 2/10/2018 002 40002 2/10/2018 2/11/2018 002 42168 2/12/2018 2/22/2018 002 43517 2/22/2018 3/31/2018 002 43517 4/1/2018 4/5/2018 002 40002 4/5/2018 4/7/2018 002 43517 4/7/2018 5/31/2018 002 43517 6/1/2018 7/31/2018 002 7/31/2018 12/31/2018 In general, I would like to insert rows to make sure there is no date gap between claims.
... View more