I have the following start/end dates and times in separate columns in the following formats.
start_date | start_time | end_date | end_time |
2019-12-26 | 1853 | 2020-01-06 | 1525 |
2019-12-30 | 1530 | 2019-12-31 | 1005 |
2019-12-31 | 1006 | 2020-01-28 | 1002 |
2019-12-30 | 2052 | 2020-01-02 | 2006 |
2019-12-30 | 843 | 2019-12-30 | 909 |
What is the most efficient way to return the difference in hours for each of the events? I need to include the decimal as well.
What time do you think the number 1,853 represents? To you want that to mean 18:53 ?
Let's assume do.
You could fix that using HMS() function and INT() and MOD() functions to get the number of hours and minutes from those strange integers.
Once you have DATE and TIME values you can make DATETIME values using DHMS() function.
data have;
input admission_dte :YYMMDD10. admission_tme separation_dte :YYMMDD10. separation_tme ;
format admission_dte YYMMDD10. separation_dte YYMMDD10. ;
datalines;
2019-12-26 1853 2020-01-06 1525
2019-12-30 1530 2019-12-31 1005
2019-12-31 1006 2020-01-28 1002
2019-12-30 2052 2020-01-02 2006
2019-12-30 843 2019-12-30 909
;
data want;
set have;
admission_tme = hms(int(admission_tme/100),mod(admission_tme,100),0);
separation_tme = hms(int(separation_tme/100),mod(separation_tme,100),0);
admission_dtm = dhms(admission_dte,0,0,admission_tme);
separation_dtm = dhms(separation_dte,0,0,separation_tme);
duration = separation_dtm - admission_dtm;
format admission_dtm separation_dtm datetime19. duration time12. admission_tme separation_tme time5.;
run;
Results
admission_ admission_ separation_ separation_ dte tme dte tme admission_dtm separation_dtm duration 2019-12-26 18:53 2020-01-06 15:25 26DEC2019:18:53:00 06JAN2020:15:25:00 260:32:00 2019-12-30 15:30 2019-12-31 10:05 30DEC2019:15:30:00 31DEC2019:10:05:00 18:35:00 2019-12-31 10:06 2020-01-28 10:02 31DEC2019:10:06:00 28JAN2020:10:02:00 671:56:00 2019-12-30 20:52 2020-01-02 20:06 30DEC2019:20:52:00 02JAN2020:20:06:00 71:14:00 2019-12-30 8:43 2019-12-30 9:09 30DEC2019:08:43:00 30DEC2019:09:09:00 0:26:00
I'm not really sure what calculation you really want to do. Do you want the hours between the end date/time of one event and the start date/time of the next event? Or do you want the hours between the start and end? Or something else?
This is how the data is formatted.
data hourdif2;
input admission_dte YYMMDD10.
admission_tme BEST6.
separation_dte YYMMDD10.
separation_tme BEST6.;
format admission_dte YYMMDD10.
admission_tme BEST6.
separation_dte YYMMDD10.
separation_tme BEST6.;
datalines;
2019-12-26 1853 2020-01-06 1525
2019-12-30 1530 2019-12-31 1005
2019-12-31 1006 2020-01-28 1002
2019-12-30 2052 2020-01-02 2006
2019-12-30 843 2019-12-30 909
;
run;
The dates are as dates but the time is either a 3 or 4 digit integer
What time do you think the number 1,853 represents? To you want that to mean 18:53 ?
Let's assume do.
You could fix that using HMS() function and INT() and MOD() functions to get the number of hours and minutes from those strange integers.
Once you have DATE and TIME values you can make DATETIME values using DHMS() function.
data have;
input admission_dte :YYMMDD10. admission_tme separation_dte :YYMMDD10. separation_tme ;
format admission_dte YYMMDD10. separation_dte YYMMDD10. ;
datalines;
2019-12-26 1853 2020-01-06 1525
2019-12-30 1530 2019-12-31 1005
2019-12-31 1006 2020-01-28 1002
2019-12-30 2052 2020-01-02 2006
2019-12-30 843 2019-12-30 909
;
data want;
set have;
admission_tme = hms(int(admission_tme/100),mod(admission_tme,100),0);
separation_tme = hms(int(separation_tme/100),mod(separation_tme,100),0);
admission_dtm = dhms(admission_dte,0,0,admission_tme);
separation_dtm = dhms(separation_dte,0,0,separation_tme);
duration = separation_dtm - admission_dtm;
format admission_dtm separation_dtm datetime19. duration time12. admission_tme separation_tme time5.;
run;
Results
admission_ admission_ separation_ separation_ dte tme dte tme admission_dtm separation_dtm duration 2019-12-26 18:53 2020-01-06 15:25 26DEC2019:18:53:00 06JAN2020:15:25:00 260:32:00 2019-12-30 15:30 2019-12-31 10:05 30DEC2019:15:30:00 31DEC2019:10:05:00 18:35:00 2019-12-31 10:06 2020-01-28 10:02 31DEC2019:10:06:00 28JAN2020:10:02:00 671:56:00 2019-12-30 20:52 2020-01-02 20:06 30DEC2019:20:52:00 02JAN2020:20:06:00 71:14:00 2019-12-30 8:43 2019-12-30 9:09 30DEC2019:08:43:00 30DEC2019:09:09:00 0:26:00
Thats it. Yes the integers represent 24hr time.
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!
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.