Creating grouping based on 10pm to 6am across consecutive dates

[simkinm2]

Hi all,

I have a dataset (ordered by date and time)  that includes a row for each time a patient has their vital signs checked, which includes the date and time of each interruption and a patient identifier: 

Date time	Patient number
7/11/19 2:49 AM	1
7/12/19 3:00 AM	1
7/12/19 3:05 AM	1
7/12/19 4:00 AM	1
7/12/19 10:39 PM	1
7/12/19 10:40 PM	1
7/13/19 2:10 AM	1
7/13/19 11:20 PM	1
7/14/19 5:00 AM	1
7/11/19 4:13 AM	2
7/11/19 5:25 AM	2
7/11/19 10:25 PM	2
7/12/19 3:30 AM	2
7/12/19 5:20 AM	2
7/12/19 5:20 AM	3
7/13/19 5:46 AM	3
7/14/19 5:57 AM	3
7/14/19 10:27 PM	3
7/15/19 3:15 AM	3

I'm trying to create a variable like "patient night" that groups each patient night together based on 10p-6a.  So if one row is 10pm on 7/11 and the next row is 2am on 7/12 they would be considered the same patient night:

Date time	Patient number	Patient night
7/11/19 2:49 AM	1	1
7/12/19 3:00 AM	1	2
7/12/19 3:05 AM	1	2
7/12/19 4:00 AM	1	2
7/12/19 10:39 PM	1	3
7/12/19 10:40 PM	1	3
7/13/19 2:10 AM	1	3
7/13/19 11:20 PM	1	4
7/14/19 5:00 AM	1	4
7/11/19 4:13 AM	2	1
7/11/19 5:25 AM	2	1
7/11/19 10:25 PM	2	2
7/12/19 3:30 AM	2	2
7/12/19 5:20 AM	2	2
7/12/19 5:20 AM	3	1
7/13/19 5:46 AM	3	2
7/14/19 5:57 AM	3	3
7/14/19 10:27 PM	3	4
7/15/19 3:15 AM	3	4

Thanks in advance! 

[ballardw]

Do you have any gaps in these "night" sequences? How are those to be treated?

Did you filter out the "day" records? That information might be needed to handle gaps of "night" values (if any).

Since you say "patient" I have to assume this is some sort of hospital data. Are any of these sequences to be affected by different "admissions"? That is often a headache with different admits overlapping in some of the data we see.

Would it be sufficient to use the first datetime value and calculate from that or do you have some more complicated rules that aren't discussed yet based on other factors like insurance, specific treatments or diagnosis or even just plain phases of the moon?

[Ksharp · Accepted solution]

I like this kind of question.

Assuming your data has been sorted by PatientNumber and Datetime.

options datestyle=mdy;
data have;
infile cards truncover expandtabs;
input Datetime & :anydtdtm32.	Patientnumber;
format Datetime mdyampm20.;
cards;
7/11/19 2:49 AM	  1
7/12/19 3:00 AM	  1
7/12/19 3:05 AM	  1
7/12/19 4:00 AM	  1
7/12/19 10:39 PM	 1
7/12/19 10:40 PM	 1
7/13/19 2:10 AM	   1
7/13/19 11:20 PM	1
7/14/19 5:00 AM	  1
7/11/19 4:13 AM	  2
7/11/19 5:25 AM	  2
7/11/19 10:25 PM	 2
7/12/19 3:30 AM  	2
7/12/19 5:20 AM  	2
7/12/19 5:20 AM	  3
7/13/19 5:46 AM	  3
7/14/19 5:57 AM	  3
7/14/19 10:27 PM  	3
7/15/19 3:15 AM	  3
;

data temp;
 set have;
 if timepart(Datetime)>='10:00:00pm't then new_Datetime=dhms(datepart(Datetime)+1,0,0,0);
  else if .<timepart(Datetime)<='06:00:00am't then new_Datetime=dhms(datepart(Datetime),0,0,0);
format  new_Datetime mdyampm20.;
run;
data want;
 set temp;
 by Patientnumber new_Datetime notsorted;
 if first.Patientnumber then want=0;
 want+first.new_Datetime;
 drop new_Datetime;
run;

[simkinm2]

This worked perfectly!  Thanks so much!

[mkeintz]

Your time range is 10PM to 6AM.  So if you subtract 6 hours from the date-time stamp, and find the resulting DATE (call it base_date), you can quickly calculate the patient_night, by determining the number of days between the current and preceding base_dates.

  
data have;
infile cards truncover expandtabs;
input Datetime & :anydtdtm32.	Patientnumber;
format Datetime mdyampm20.;
cards;
7/11/19 2:49 AM	  1
7/12/19 3:00 AM	  1
7/12/19 3:05 AM	  1
7/12/19 4:00 AM	  1
7/12/19 10:39 PM	 1
7/12/19 10:40 PM	 1
7/13/19 2:10 AM	   1
7/13/19 11:20 PM	1
7/14/19 5:00 AM	  1
7/11/19 4:13 AM	  2
7/11/19 5:25 AM	  2
7/11/19 10:25 PM	 2
7/12/19 3:30 AM  	2
7/12/19 5:20 AM  	2
7/12/19 5:20 AM	  3
7/13/19 5:46 AM	  3
7/14/19 5:57 AM	  3
7/14/19 10:27 PM  	3
7/15/19 3:15 AM	  3
;

data want (drop=basedate) ;
  set have;
  by patientnumber;

  basedate=datepart(datetime-'06:00:00't);
  format basedate date9. ;

  patient_night+intck('day',lag(basedate),basedate);
  if first.patientnumber=1 then patient_night=1;
run;