topic Re: Numeric to Date Conversion in SAS Programming

Numeric to Date Conversion

iSAS — Tue, 04 May 2021 12:34:17 GMT

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. ?

Re: Numeric to Date Conversion

PaigeMiller — Tue, 04 May 2021 12:36:17 GMT

In a DATA step, or in PROC DATASETS, assign the datetime16. format to this variable.

Re: Numeric to Date Conversion

iSAS — Tue, 04 May 2021 12:39:04 GMT

I did this after the proc import but it didn't work:
Data raw1;
Set raw (rename=(LastAccessTimeStamp=LastAccessTimeStamp1));
LastAccessTimeStamp=input(put(LastAccessTimeStamp1,8.6),datetime16.);
run;

Re: Numeric to Date Conversion

PaigeMiller — Tue, 04 May 2021 12:40:33 GMT

You want to assign a format to the variable. INPUT(PUT()) doesn't seem like it should work, to me.

Re: Numeric to Date Conversion

iSAS — Tue, 04 May 2021 12:45:46 GMT

I also did this after the proc import but all the values just turned into 01JAN60:hh:mm:ss
Data raw1;
Set raw;
format LastAccessTimeStamp datetime16.;
run;

Re: Numeric to Date Conversion

jonas_bilenas — Tue, 04 May 2021 12:48:40 GMT

DATA raw;

SET raw;

FORMAT LastAccessTimeStamp datetime16.;

RUN;;

Re: Numeric to Date Conversion

PaigeMiller — Tue, 04 May 2021 12:58:22 GMT

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;

Re: Numeric to Date Conversion

Tom — Tue, 04 May 2021 12:59:51 GMT

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;