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!
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;
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?
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;
This worked perfectly! Thanks so much!
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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.