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
| 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;
data have;
input number $ actualdt:mmddyy10. actualoff:mmddyy10.;
cards;
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
;
run;
data want;
set have;
by number;
prev_actualoff=lag(actualoff);
if first.number or prev_actualoff<actualdt then elapsed_time=0;
elapsed_time+(actualoff-actualdt);
drop prev_acutaloff;
run;
Thanks so much for your help. The only issue now is I cannot tell which ones are the result of a single elapsed time or if it is a cumulative lapse time from multiples. How can I show only the total if it is cumulative lapse times, similar to below?
1 0003530 20993 21019 . 26
2 0003530 21043 21056 21019
3 0003530 21056 21059 21056
4 0003530 21059 21063 21059
5 0003530 21063 21066 21063
6 0003530 21066 21073 21066
7 0003530 21073 21124 21073
8 0003530 21123 21126 21124
9 0003530 21126 21138 21126
10 0003530 21138 21161 21138 119
11 0003530 21164 21231 21161 67
Just add another counter to the code that I produced, which instead of counting elapsed time, counts the number of records.
April 27 – 30 | Gaylord Texan | Grapevine, Texas
Walk in ready to learn. Walk out ready to deliver. This is the data and AI conference you can't afford to miss.
Register now and lock in 2025 pricing—just $495!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.