SAS - Excel Import DateTime not retaining enough significant digits

Question

As the title states, I'm having issues importing datetimes from Excel to SAS. The issue seems to be with seconds:

here's sample data from excel:

My DateTime:

Here's the raw excel numbers:

Edit2:

43417.58657407
43417.58656250

When I'm import these into SAS, this is how SAS is displaying them:

13NOV2018:14:04:39
13NOV2018:14:04:39

and the numeric values:

1857737079
1857737079

I'm trying to figure out how to get SAS to read the seconds correctly. I'm using proc import and here's my code:

proc import
out = MyDSOutput
datafile= MyDSInput
dbms     = EXCEL replace;
sheet    = "page";
getnames = yes;
mixed    = yes;
scantext = yes;
usedate  = no;
scantime = yes;
textsize = 32767;
;
run;

EDIT: I should have added that converting this to a CSV really isn't an option because I have numeric IDs that are >15 digits and excel will convert anything >15 digits to 0s.

EDIT2: Added an expanded version of the raw excel numbers

Did you try using the XLSX database engine (or XLS if that is they type of Excel file you have) instead of EXCEL engine? — Tom
Can you ask Excel to display more decimal places for the actual value it has stored? And paste as text instead of pictures into your question. — Tom
@Tom Haven't tried XLSX engine, I'm looking into that now. I did update the post to include all the digits that excel has stored. — DukeLuke
What happens if you store just the time of day part as its own column in the sheet? Does that come across and display using the same second of the day? — Tom
@Tom I changed my proc import from dbms = excel to dbms = xlsx and this seemed to fix the issue. I also attempted the XLSX engine through a libname statement and that seemed to do the trick. I'll probably just stick with proc import though. Thank you! — DukeLuke

Tom Tom · Accepted Answer · 2019-04-01T14:04:11

Excel stores time as a fraction of a day. It is impossible to represent a specific number of seconds exactly when you do that. That particular time, 14:04:40 (second number 50,680), is particularly hard to represent as a floating point fraction. If you represent it as 0.58657407 and multiple by the number of seconds in a day you get seconds of 50,679.999648, so slightly less that what you want.

Try splitting your DATETIME field into separate DATE and TIME fields. That way SAS will have more binary digits to represent just the time of day (since it doesn't also have to have the 40K or 20K seconds for the day of the year). Perhaps that will get close? Or store the value as a character string in Excel and then use INPUT() function in SAS to convert the string to a datetime value.

For your ID issue, do not store ID values as numbers, in any system if you can avoid it. If you can store the ID in your EXCEL file then EXCEL can write it the the CSV file. But when you read the CSV file make sure to read that column into a character variable and not a numeric one.

SAS - Excel Import DateTime not retaining enough significant digits

1 Answers