Hello,
I need some help in converting time to numeric value, well not numeric value but convert 00:10:00 to 10 or 00:10:12 to 10.12 in SAS.
I tried using substr first to get only 10:00 but it didn't work.
Data dummy;
set test;
time=substr(time1, 3,5); /* To get only 10:00, starting from 3rd digit and get five digits*/
run;
I don't want to convert 00:10:00 as 600 which is the numeric value for 10 minutes. I want to take out the colons and just get the number.
Any help!!
Thank you
M
If you truly have a time value to begin (and not a character string), the accurate conversion formula would be:
minutes = timevalue / 60;
If you have a time value of 612 that is formatted as 10:12, and you want a not-so-accurate conversion formula to something like 10.12, you can use:
minutes = int(timevalue/60) + 0.01 * mod(timevalue, 60);
I think you have confused the format of a variable and the value of a variable, these are not the same. Even though you see 00:10:12, the underlying value is in seconds. If you want to change 00:10:12 to 10.12, I don't think you did the math properly, the underlying value is 60*10+12 (60 seconds times 10 minutes, plus another 12 seconds). If you want this in minutes, you divide 60*10+12 by 60, and that is not 10.12.
I want to take out the colons and just get the number.
Remove the format.
format time;
Then you see the value 612. Which was the value all along, the format only changed the way it appeared, it did not change the value itself.
Hi PaigeMiller,
I think you are right, if I remove the format, I get it in numbers but I want to see if I can just replace colon with a period and get the value as it is. Instead of 10:12 replace colon with period. Maybe using 'compress' function I can do that, not sure.
@Malathi13 wrote:
Hi PaigeMiller,
I think you are right, if I remove the format, I get it in numbers but I want to see if I can just replace colon with a period and get the value as it is. Instead of 10:12 replace colon with period. Maybe using 'compress' function I can do that, not sure.
You cannot replace the colon with a period. You are still confusing the format with the value. The value is 612. You could create a mathematical formula in SAS to convert 612 to 10.12, I will leave that up to you to figure out how to do that.
Convert to a character using PUT()
Use COMPRESS() to remove colon
Use INPUT() to read back in as integer.
If you truly have a time value to begin (and not a character string), the accurate conversion formula would be:
minutes = timevalue / 60;
If you have a time value of 612 that is formatted as 10:12, and you want a not-so-accurate conversion formula to something like 10.12, you can use:
minutes = int(timevalue/60) + 0.01 * mod(timevalue, 60);
Hi Astounding,
Thank you so so much!! Perfect conversion, I got 10:12 as 10.12
M
@Malathi13 wrote:
Thank you so so much!! Perfect conversion, I got 10:12 as 10.12
And don't try to do any math with this value 10.12, because although it's a number, it's not really a number that can be added or subtracted, etc.
Hi @Astounding ,
I am having the same issue as well. Tried below solution but could not make it to work for my case.
I have only two variables, DOSE_DT_TIME and PT. Every PT is supposed to take 2 doses daily with 12 hours difference. Paige Miller helped to calculate the difference between every two doses per PT and the difference is perfect. So with the above two original variables I have two additional variables now: Difference between the two dates formatted as TIME and non-formatted as difference in SECONDS (i.e., 12:09:00 and 43740). I need a new variable to calculate the difference in hours as number (i.e., 12.09 as 12 hours and 9 minutes). Last column below is calculating wrong number of hours.
Any help will be greatly appreciated. Thank you so much.
PT | DOSE_DT_TIME | DIFF_FRMTD | DIFF_IN_SEC | DIFF_IN_HRS |
1 | 10/1/2021 21:52 | 12:52:00 | 46320 | 12 |
1 | 10/2/2021 10:30 | 12:38:00 | 45480 | 12 |
1 | 10/2/2021 22:36 | 12:06:00 | 43560 | 12 |
1 | 10/3/2021 10:34 | 11:58:00 | 43080 | 11 |
1 | 10/3/2021 21:42 | 11:08:00 | 40080 | 11 |
2 | 10/2/2021 11:25 | |||
2 | 10/2/2021 20:00 | 8:35:00 | 30900 | 8 |
2 | 10/3/2021 8:00 | 12:00:00 | 43200 | 12 |
2 | 10/3/2021 20:00 | 12:00:00 | 43200 | 12 |
2 | 10/4/2021 8:00 | 12:00:00 | 43200 | 12 |
2 | 10/4/2021 20:00 | 12:00:00 | 43200 | 12 |
Her is part of the code.
DIFF_IN_SEC = DOSE_DT_TIME- LAG(DOSE_DT_TIME);
DIFF_IN_HRS = INT(DIFF_IN_SEC /3600) + 0.01 * MOD(DIFF_IN_SEC , 60);
To convert seconds to hours just divide by the number of seconds in an hour.
diff_hours = diff_in_sec / '01:00't ;
@SAS_USER_928 wrote:
Hi Tom,
I tried that as well already but for some reason it did not give me correct results, e.g., formatted difference 11:58 was displayed as 11.96 instead of 11.58.
Thanks
It makes no sense to store 11.58 as a number. 11 hours and 58/100ths of an hour is a totally different number than 11 hours and 58/60ths of an hour.
If you want to make a character string with a period instead of a colon you could use the TRANSLATE() function to convert the colon to a period.
hour_string = translate( put(time_value,tod5.) , '.' , ':' );
Or depending on your LOCALE setting you might be able to just use the NLTIME format. https://go.documentation.sas.com/doc/en/vdmmlcdc/8.1/leforinforref/p0ywh4fysyz7yyn1wl67rz6zogmt.htm to display time using period instead of colon as the separator between the hours and minutes.
SAS contains a format to print the number of seconds as HHMM. You don't need to calculate hours once you know the number of seconds.
format diff_in_sec hhmm5. ;
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.