topic Re: SAS changed datetime column of my xlsx data in SAS Procedures

SAS changed datetime column of my xlsx data

febyinkasid — Fri, 22 Mar 2024 08:19:59 GMT

Hi, so I was importing XLSX file with script below:

proc import out=test

datafile="&paath.\Module.xlsx"

dbms=xlsx replace;

run;

the import is successful but when I check the SAS file test, my datetime data converted into a date only. below is the xlsx file:

Converted into date only in SAS with yymmdd format:

Anyone has solution on how to get the field into datetime original?

Re: SAS changed datetime column of my xlsx data

Kurt_Bremser — Fri, 22 Mar 2024 08:43:52 GMT

In Excel, there is no difference between dates and times on a technical level. Dates are stored as integers, while times are always stored as fraction of a day, so a date can always be considered a datetime with a time part of 00:00:00.

If PROC IMPORT finds only integers (and a date-only format like you show), it will assume this to be a pure date column.

To make these datetime in SAS, do

start = dhms(start,0,0,0);
end = dhms(end,0,0,0);
format start end e8601dt19.; /* or any other format you like */

Alternatively, multiply the values by 86400 (number of seconds in a day). This would also keep "hidden" time parts.

Re: SAS changed datetime column of my xlsx data

febyinkasid — Fri, 22 Mar 2024 09:03:17 GMT

Thank You so much!

Re: SAS changed datetime column of my xlsx data

Tom — Fri, 22 Mar 2024 13:34:15 GMT

The DHMS() function will also keep the fractions from the DATE value passed to it.

6    data test;
7      date=datetime()/'24:00't;
8      dt=dhms(date,0,0,0);
9      put date=comma20.2 date=date9. dt=datetime19.;
10   run;

date=23,457.40 date=22MAR2024 dt=22MAR2024:09:33:13
NOTE: The data set WORK.TEST has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds