Proc Import from Excel Datetime convert to ridiculous long number

[wbsjd]

Hello Everyone~

I am reading data from Excel to SAS.  In excel the requested Date is like the the below, a date time format,

However, after I Proc Import the Excel file to SAS, the time became a long ridiculous number. 

Can anybody help me to solve this problem? 

Thank you so much!

[Kurt_Bremser · Accepted solution]

What you get is the raw number Excel uses to store datetime values. To the left of the decimal point is the number of days since 1899-12-30 (which is day 0), to the right is the time as fraction of a day.

Add '30dec1899'd to the integer value, multiply the fractional part by 86400, and use both numbers as day and seconds argument for the DHMS function.

req_date = dhms(int(req_date)+'30dec1899'd,0,0,(req_date-int(req_date))*86400);
format req_date datetime20.;

[wbsjd]

Thank you so much for the solution, it works perfectly

[ballardw]

You appear to have also done something else to make a character value from what should have been numeric.

[Tom]

That is normally caused by having a column in the spreadsheet that mixes numeric and character cells.  When that happens SAS is forced to create a character variable (otherwise it could not store the character values).  So the numeric values get stored as digit strings representing the number.

To convert it back to a datetime value first use INPUT() to convert it to a number and then add in the SAS date for 30DEC1899 to adjust for differences in how the two systems number days.  You can use the DHMS() function then to convert it from days (with fractions of day) into seconds that SAS uses to store datetime values.

data want;
  set have;
  dt = dhms(input(requested_date,32.)+'30dec1899'd,0,0,0);
  format dt datetime19.;
run;

[wbsjd]

Thank you so much for your solution and explanation. It's really helpful for me!