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 |
@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
Which of these variables are numeric? Which of these variables are character?
None of them are character. The dates are dates and the times are simple numbers.
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)
Hi Tom.
In a 24-hour clock 2222 is actually 10:22 PM while 1022 would be 10:22 AM.
@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
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).
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.
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 ;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.