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!
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.;
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.;
You appear to have also done something else to make a character value from what should have been numeric.
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;
Thank you so much for your solution and explanation. It's really helpful for me!
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.