Hour Difference with Data Spread Over 4 Columns

[cobba]

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.

[PaigeMiller]

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?

[cobba]

Looking for the difference between the values accross the rows. Each row is independent from each other so no need to calculate the difference between rows.

[Reeza]

1. Create two datetime fields (one for start and one for end) - use DHMS function. May need to do some type conversions but you haven't provided the data types/formats for each column.
2. Subtract the two
3. Format the variable as a time for hours/minutes. If you need 25.5 hours which is 25:30 then you'll need to do some basic math there as well.

If you need help with code, please provide sample data in the form of a data step ensuring that your data types match your data types. That's usually the biggest issue with these types of questions.

[cobba]

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

[Tom · Accepted solution]

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

[cobba]

Thats it. Yes the integers represent 24hr time.