BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Jeff_DOC
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

@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

 

 

View solution in original post

9 REPLIES 9
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Jeff_DOC
Pyrite | Level 9

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

Tom
Super User Tom
Super User

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)

 

 

 

Jeff_DOC
Pyrite | Level 9

Hi Tom.

 

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

Tom
Super User Tom
Super User

@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

 

 

Jeff_DOC
Pyrite | Level 9

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).

PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Jeff_DOC
Pyrite | Level 9
It would be nice if I could assign a correct response to you as well. Thank you so much, it worked perfectly.
Tom
Super User Tom
Super User

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 ;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

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!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 4335 views
  • 1 like
  • 3 in conversation