Character dates and times to numeric dates and times

[stancemcgraw]

I have a number of character dates and time variables that need to be formatted to numeric dates and times. Please see examples below:

1) Arrival date:  01/02/2018 (is in character and formatted as $11.)

2) Arrival time: .4215277777777778 (is in character and formatted as $21.)

3)Radiology date/time:  01/04/2018 08:30 (is in character and formatted as $19.)

4) Read date: 43102 (is in character and formatted as $10.)

I'd love all of the dates to be in mmddyy`10. and all of the times to be in 24hr time. Dates are so challenging. Please help!

[PaigeMiller]

1. Please clarify if this is January 2, 2018 or February 1, 2018.

2. Convert to number, multiply by the number of seconds in a day, then apply the TIME format

3. Please clarify if this is January 4, 2018 or April 1, 2018

4. Did you get this from Excel? If so, convert to numeric then convert to a SAS date (there are plenty of discussions in this forum about how to convert Excel dates to SAS dates).

[Tom]

Did you get these strings be converting an EXCEL spreadsheet into a SAS dataset?   If so can you clean up the spreadsheet so it has only ONE type of value in each column? When converting a spreadsheet, where each CELL is independent, into a dataset, where each variable (aka column) must have only one type, then any column of cells that has mixed types must be converted to character to preserve the character strings.

The 2nd and 4th items are examples of how EXCEL will store TIME and DATE values and how SAS would then store them as character string when it has to store those numbers into a character variable.

Note that the FORMAT attached to the character is much less important than the storage LENGTH of the character variable.  So let's just assume that for your four examples the LENGTH of the variables matched the displayed WIDTH that the format produces.

1) Arrival date:  01/02/2018 (length $11)

datevar = input(string,mmddyy10.);

If you really have any strings of length 11 then they might not work.  You will want to look at those strings and see why they have a length of 11.  Perhaps they have some extra spaces?  In which case you might want to use COMPRESS() to remove those first so you don't ignore the last digit of the year.

If you want the first number to be the day of the month instead then use DDMMYY informat instead.

2) Arrival time: .4215277777777778 (length $21)

Just convert to a number and multiply by 24 hours.  Remember the INPUT() function does not care if the width of the informat being used it longer than the string being read.  32 is the maximum width that the normal numeric informat will allow.

time_var = '24:00:00't * input(string,32.);

3)Radiology date/time:  01/04/2018 08:30 (length $19)

You can use the ANYDTDTM informat to read this, but you will want to set the system option DATESTYLE to the appropriate value so it knows whether to create the first of April or January fourth from that string.

options datestyle=MDY;
....
datetime_var = input(string,anydtdtm19.);

4) Read date: 43102 (length $10)

So that looks like it might be number of days since 1900, which how EXCEL normally stores dates.

date_var = input(string.32.) + '30DEC1899'd ;

Note you can also optionally ask EXCEL to count dates from 1904 instead which eliminates the goofy extra leap day issue.  So if it looks like your dates are off by almost exactly four years try using this formula instead.

date_var = input(string.32.) + '31DEC1903'd ;

Remember to attach an appropriate FORMAT to your new numeric variables so they will print in a style that humans can understand.  Avoid displaying dates in either MDY or DMY order has which ever one you pick will confuse half of your audience.

[Quentin]

Good tip @Tom , thanks.

I didn't realize Windows Excel had an option to use 1904 as day 0, but there it is:

https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b48...

Makes me wonder if all the SAS engines that read excel are smart enough to honor this option.

I never learned the history of why Mac Excel used the 1904 base date.  The internet says that microsoft intentionally coded the 1900 leapyear bug into Excel to make it compatible with Lotus 1-2-3.  But maybe when they got around to making a Mac version, Steve Jobs wasn't convinced it was a helpful bug, so went with 1904?