topic Re: Finding the difference between dates and times in SAS Programming

Finding the difference between dates and times

Jeff_DOC — Wed, 05 Apr 2023 17:23:11 GMT

Good morning.

I'm looking for advice on how to find the difference between dates and time. The wrinkles are each date and time are in separate fields and the time is in 24-hour clock format. As you can see from the column ATTEMPT_1 I haven't been all that successful.

For the attempt I did try dhms(date_1, 0, 0, time_1) as attempt_1 format = datetime15. but obviously that did not work. If I could just get the date time conversion to work, I could then determine the number of hours between the two date-time variables.

What do you think? Is there an efficient way to do this?

PERSON	DATE_1	TIME_1	DATE_2	TIME_2	ATTEMPT_1
100	5/7/2018	2222	5/9/2018	1008	07MAY18:00:37
100	5/9/2018	1009	5/15/2018	1430	09MAY18:00:16
101	9/13/2020	957	9/13/2020	1046	13SEP20:00:15
101	9/13/2020	1047	9/21/2020	1541	13SEP20:00:17
101	1/19/2023	2200	1/21/2023	1336	19JAN23:00:36
102	5/17/2012	2306	5/18/2012	1530	17MAY12:00:38
102	7/23/2012	2257	7/24/2012	945	23JUL12:00:37
102	12/5/2012	1749	12/6/2012	1503	05DEC12:00:29
103	11/9/2014	808	11/11/2014	1237	09NOV14:00:13
104	6/21/2008	1840	6/23/2008	1600	21JUN08:00:30
105	2/27/2015	1539	2/28/2015	630	27FEB15:00:25
105	2/28/2015	631	3/8/2015	1005	28FEB15:00:10
106	12/15/2020	1918	1/21/2021	1118	15DEC20:00:31
107	4/15/2013	1940	4/17/2013	1510	15APR13:00:32
108	6/6/2009	37	6/7/2009	928	06JUN09:00:00
109	12/15/2020	1918	12/24/2020	1530	15DEC20:00:31
110	1/26/2023	2254	1/30/2023	1445	26JAN23:00:37
111	3/21/2012	2212	3/22/2012	1439	21MAR12:00:36
111	4/18/2012	1630	4/23/2012	800	18APR12:00:27
112	4/16/2017	915	4/16/2017	1548	16APR17:00:15
112	4/16/2017	1548	4/19/2017	1700	16APR17:00:25
113	6/22/2016	255	6/22/2016	449	22JUN16:00:04
113	6/22/2016	508	6/28/2016	1525	22JUN16:00:08
114	8/19/2020	1352	8/20/2020	1150	19AUG20:00:22
115	9/25/2009	1621	10/7/2009	1430	25SEP09:00:27

Re: Finding the difference between dates and times

PaigeMiller — Wed, 05 Apr 2023 17:41:43 GMT

Which of these variables are numeric? Which of these variables are character?

Re: Finding the difference between dates and times

Tom — Wed, 05 Apr 2023 17:53:05 GMT

Your TIME1 values are clearly not actually time values. For example the first observation has 2222 as TIME1. So assuming the variable is numeric so that it contains the number 2,222 then if you treat that as a TIME value (number of seconds since midnight) it means 37 minutes and 2 seconds.

You could convert TIME1 into a string and then use INPUT() to convert the string into a date.

But since you are already using the DHMS() function why not just use a little arithmetic to tease out the hours and minutes instead.

dhms(date_1, int(time_1/100),mod(time_1,100),0)

Re: Finding the difference between dates and times

Jeff_DOC — Wed, 05 Apr 2023 18:04:24 GMT

Hi Tom.

In a 24-hour clock 2222 is actually 10:22 PM while 1022 would be 10:22 AM.

Re: Finding the difference between dates and times

Jeff_DOC — Wed, 05 Apr 2023 18:05:52 GMT

None of them are character. The dates are dates and the times are simple numbers.

Re: Finding the difference between dates and times

Tom — Wed, 05 Apr 2023 18:44:43 GMT

@Jeff_DOC wrote:

Hi Tom.

In a 24-hour clock 2222 is actually 10:22 PM while 1022 would be 10:22 AM.

???

The time 10:22 PM is the same number as the time 22:00.

But the number used to represent that time is not the number 2,222 that you have in your dataset. The number used to represent 22:22 is the number 80,250.

2052  data test;
2053    time='22:22't ;
2054    put time=comma12. +1 time time5.;
2055  run;

time=80,520  22:22

But if you take the remainder of the number 1,234 when divided by 100 you get 34. And if you divide 1,234 by 100 and take just the integer part you get the number 12. So you have HOURS=12 and MINUTES=34.

2074  data test;
2075    number = 1234 ;
2076    hours=int(number/100);
2077    minutes=mod(number,100);
2078    format number comma7. hours minutes z2.;
2079    put (_all_) (=);
2080  run;

number=1,234 hours=12 minutes=34

Another way to convert a number like 1,234 into an actual TIME value you could convert it to a string and use the INPUT() function to convert the string into a time value. time=input(put(number,z4.),hhmmss4.)

2061  data test;
2062    number = 1234 ;
2063    time=input(put(number,z4.),hhmmss4.);
2064    format number comma7. time time8.;
2065    put (_all_) (=);
2066  run;

number=1,234 time=12:34:00

Re: Finding the difference between dates and times

Jeff_DOC — Mon, 10 Apr 2023 16:51:53 GMT

That worked great for creating and splitting out hours and minutes on a 24-hour clock, so thank you.

Since no good deed goes unpunished the user now has a follow up. How would I subtract the date and time for each record to find the difference? For instance, PERSON 100 began on 5/7/2018 at 2222 (10:22 PM) and completed on 5/9/2018 at 1008 (10:08 AM). That means they spent 1.49 days in program (or 35.76667 hours).

Re: Finding the difference between dates and times

PaigeMiller — Mon, 10 Apr 2023 17:02:25 GMT

So you have a valid SAS time variable to represent 10:08 AM and your other times, calculated via

time=input(put(number,z4.),hhmmss4.);

Now you need a valid DATE/TIME value. Using the variable names in your original data set:

time1=input(put(time_1,z4.),hhmmss4.);
time2=input(put(time_2,z4.),hhmmss4.);
datetimevalue1=dhms(date_1,hour(time1),minute(time1),0);
datetimevalue2=dhms(date_2,hour(time2),minute(time2),0);
difference=datetimevalue2-datetimevalue1;

This difference is in seconds. To convert this to your 1.49 days, you would divide by the proper constant. To convert this to your 35.76667 hours, you would divide by a different constant that converts seconds to hours.

Re: Finding the difference between dates and times

Jeff_DOC — Mon, 10 Apr 2023 17:38:39 GMT

It would be nice if I could assign a correct response to you as well. Thank you so much, it worked perfectly.

Re: Finding the difference between dates and times

Tom — Tue, 11 Apr 2023 11:36:38 GMT

Convert your date (number of days) and time(number of seconds) to a datetime value(number of seconds).

datetime=dhms(date,0,0,time);

Once you have two datetime values you can just subtract them to find the difference in seconds. To convert from seconds to days just divide by the number of seconds in a day.

days_diff = (datetime2 - datetime1) / '24:00:00't ;