BookmarkSubscribeRSS Feed
jgadams2
Calcite | Level 5

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
000353006/23/1707/19/17
000353008/12/1708/25/17
000353008/25/1708/28/17
000353008/28/1709/01/17
000353009/01/1709/04/17
000353009/04/1709/11/17
000353009/11/1711/01/17
000353010/31/1711/03/17
000353011/03/1711/15/17
000353011/15/1712/08/17
000353012/11/1702/16/18
000353002/28/1803/20/18
000353003/26/1804/05/18
000353006/11/1806/14/18
000353007/09/1807/11/18
000353007/24/1807/28/18
000363501/16/1801/21/18
000363503/12/1803/15/18
000363503/15/1803/20/18
000363505/24/1806/08/18
000363506/08/1806/21/18
000363506/21/1806/28/18
000363507/05/1807/08/18
000363507/08/1807/15/18
000365803/19/1803/22/18
000365803/22/1804/03/18
000365804/03/1804/11/18
000391807/11/1707/13/17
000397011/10/1711/13/17
000400904/05/1709/08/17
000400909/08/1712/04/17
000409206/21/1707/10/17
000409208/31/1709/05/17
000409204/20/1804/23/18
000409204/23/1805/01/18
000409205/01/1805/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;

 

3 REPLIES 3
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller
jgadams2
Calcite | Level 5

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

 

 

 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 3 replies
  • 577 views
  • 0 likes
  • 2 in conversation