topic Re: How do I Counting Number of Days between Consecutive observations in New SAS User

How do I Counting Number of Days between Consecutive observations

jgadams2 — Tue, 10 Sep 2019 13:13:08 GMT

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;

Re: How do I Counting Number of Days between Consecutive observations

PaigeMiller — Tue, 10 Sep 2019 13:39:55 GMT

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;

Re: How do I Counting Number of Days between Consecutive observations

jgadams2 — Tue, 10 Sep 2019 16:15:38 GMT

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

Re: How do I Counting Number of Days between Consecutive observations

PaigeMiller — Tue, 10 Sep 2019 16:55:04 GMT

Just add another counter to the code that I produced, which instead of counting elapsed time, counts the number of records.