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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.