How do I bracket time periods using dates of events within a confined study period?

[Kels123]

Hi,

I am doing a study where I look at how subjects move between two different environments (e.g. hospital and home) over time. All dates that correspond to an admission to the hospital are indicated by time=0, and dates that correspond to released from the hospital are indicated by time=1 (so the value of the time variable alternates between 0 and 1 for each row). 

There is also a variable "period" that represents the order of these events for each subject.

If someone moves from the hospital to the home and then back to the hospital, they will have time=0, 1, and then 0 again, and period= 1, 2, then 3.

Please see the attachment for example of what the data output currently looks like (note that there are subject ID numbers that I have truncated to protect confidentiality).

I would like to use the movement dates to create time periods with a start date and end date. Taking the first subject, for example, with the following data: 

Time Period MovementDate (MvmtDate)

0         1        01/31/2007

1         2        05/01/2008

My research study period is 01/01/2007 - 12/31/2014. I would like to transform this subject's data into the following (new/manipulated data/variables are bolded):

Time Period PeriodStartDate                        PeriodEndDate (MvmtDate - 1)

1         0        01/01/2007 (study start date)    01/30/2007

0         1        01/31/2007                                04/30/2008

1         2        05/01/2008                                12/31/2014 (study end date, in this case date of censoring)

Do you have any suggestions? I started off by making a separate database to create the first time period (starting 01/01/2007) which I planned to merge in. Then I thought I could create two separate databases to get start and end dates (e.g. 01/31/2007 and 04/30/2008) and merge together into the appropriate rows by offsetting the period variable. But then I realized that creating the last time period (ending with 12/31/2014) might be difficult, and my code is starting to get very complicated. Perhaps there is a way to do this using PROC SQL? Any suggestions would be greatly appreciated. Thank you!

[Reeza]

Please post some sample data, fake data is fine, but in your post vs as an attachment and as text not an image. Providing an image means we'd have to type out the data and I won't do that. Make sure to include several use cases that you're likely to encounter and expected output. 

[Kels123]

Hello,

Sorry about that. As requested, here is some fake sample data that represent multiple different possible scenarios:

Obs ParticipantID Time Period MvmtDate

1 00001 0 1 01/31/2007

2 00001 1 2 05/01/2008

3 00002 0 1 06/01/2012

4 00002 1 2 05/24/2013

5 00003 1 1 02/16/2007

6 00003 0 2 05/06/2010

7 00003 1 3 08/19/2010

8 00004 0 1 06/15/2007

9 00004 1 2 06/21/2008

10 00005 0 1 04/27/2007

11 00005 1 2 11/07/2008

12 00006 0 1 03/15/2007

13 00006 1 2 06/20/2007

14 00007 0 1 08/24/2015

15 00008 0 1 04/17/2007

16 00008 1 2 04/19/2007

17 00009 0 1 05/10/2007

18 00009 1 2 08/03/2007

19 00010 0 1 07/31/2007

20 00010 1 2 08/28/2007

21 00010 0 3 02/20/2008

22 00010 1 4 03/05/2008

23 00011 0 1 02/05/2008

24 00011 1 2 03/12/2010