I have a .xlsx file that has this column:
LastAccessTimeStamp
3/23/2021 14:15
44278.89847
When I do proc import, the output was this:
LastAccessTimeStamp
44278.59403
44278.89847
May I know what should be done to convert it from numeric to date with format datetime16. ?
In a DATA step, or in PROC DATASETS, assign the datetime16. format to this variable.
You want to assign a format to the variable. INPUT(PUT()) doesn't seem like it should work, to me.
DATA raw;
SET raw;
FORMAT LastAccessTimeStamp datetime16.;
RUN;;
These are neither SAS date values nor SAS datetime values. So you will have to do the conversion and then assign the proper formats.
Data raw1;
set raw;
time=(lastaccesstimestamp-floor(lastaccesstimestamp))*24*60*60; /* Convert decimal part of the date into seconds */
date=floor(lastaccesstimestamp)-21916; /* Convert Excel date to SAS date */
format date date9. time time.;
run;
So it looks like you are trying to say the cells in the XLSX file for that column have numbers like 44,278.89847 that when displayed using one of Excel's datetiime formats looks like the string '3/23/2021 14:15'. Excel stores datetime values as the number of days and fractions of a day since 1900.
If all of the cells in that column had values like that then SAS would have created a numeric value with datetime values converted to the SAS equivalent number of seconds since 1960. If it did that then the values would NOT look like 44278.89847.
So I assume that what happened is that one or more cells in that column contained a character string. So SAS defined LastAccessTimeStamp as a character variable and stored the numbers as a digit string that represents that number.
To fix that convert the string to a number, adjust for difference in starting day between the two systems, and convert the days + fraction of day into seconds.
data want;
set have;
format real_LastAccessTimeStamp datetime20. ;
real_LastAccessTimeStamp= dhms(input(LastAccessTimeStamp,32.)+'30DEC1899'd,0,0,0);
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: