How do I determine the number of days between each consecutive stay for each number listed. I have multiple situations that could be true;
For the first one (0003530) listed I need to know that there are 26 days between actiondt and actualoff, since there is a break between 7/19/17 and 8/12/17 I need it to start counting over again. The next 9 either have the actualoff less than or equal to the next actiondt I need to know the time between those observations . 8/25/17-12/8/17 There is a break between the date of the next one 12/8/17- 12/11/17 so I need it to start counting again. Continue that to the end of that number and start the process over every time the number changes.
This is the data I have
number actualdt actualoff
0003530 | 06/23/17 | 07/19/17 |
0003530 | 08/12/17 | 08/25/17 |
0003530 | 08/25/17 | 08/28/17 |
0003530 | 08/28/17 | 09/01/17 |
0003530 | 09/01/17 | 09/04/17 |
0003530 | 09/04/17 | 09/11/17 |
0003530 | 09/11/17 | 11/01/17 |
0003530 | 10/31/17 | 11/03/17 |
0003530 | 11/03/17 | 11/15/17 |
0003530 | 11/15/17 | 12/08/17 |
0003530 | 12/11/17 | 02/16/18 |
0003530 | 02/28/18 | 03/20/18 |
0003530 | 03/26/18 | 04/05/18 |
0003530 | 06/11/18 | 06/14/18 |
0003530 | 07/09/18 | 07/11/18 |
0003530 | 07/24/18 | 07/28/18 |
0003635 | 01/16/18 | 01/21/18 |
0003635 | 03/12/18 | 03/15/18 |
0003635 | 03/15/18 | 03/20/18 |
0003635 | 05/24/18 | 06/08/18 |
0003635 | 06/08/18 | 06/21/18 |
0003635 | 06/21/18 | 06/28/18 |
0003635 | 07/05/18 | 07/08/18 |
0003635 | 07/08/18 | 07/15/18 |
0003658 | 03/19/18 | 03/22/18 |
0003658 | 03/22/18 | 04/03/18 |
0003658 | 04/03/18 | 04/11/18 |
0003918 | 07/11/17 | 07/13/17 |
0003970 | 11/10/17 | 11/13/17 |
0004009 | 04/05/17 | 09/08/17 |
0004009 | 09/08/17 | 12/04/17 |
0004092 | 06/21/17 | 07/10/17 |
0004092 | 08/31/17 | 09/05/17 |
0004092 | 04/20/18 | 04/23/18 |
0004092 | 04/23/18 | 05/01/18 |
0004092 | 05/01/18 | 05/02/18 |
What I have tried and not getting what I need.
DATA MORE1B;
RETAIN INDATE . LASTDATE .;
SET MORE1A;
BY NUMBER;
IF FIRST.NUMBER THEN DO;
LASTDATE=ACTUALOFF;
FORMAT LASTDATE MMDDYY8.;
INDATE=ACTIONDTON;
FORMAT INDATE MMDDYY8.;
DAYS=LASTDATE-INDATE;
END;
ELSE DO;
IF LASTDATE = ACTIONDTON THEN DO;
FORMAT INDATE MMDDYY8.;
LASTDATE= ACTUALOFF;
END;
ELSE IF LASTDATE < ACTIONDTON THEN DO;
FORMAT INDATE MMDDYY8.;
LASTDATE= ACTUALOFF;
END;
ELSE IF INDATE > ACTIONDTON THEN DO;
DAYS=LASTDATE-INDATE;
INDATE=ACTIONDTON;
END;
END;
OUTPUT;
RUN;