BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
febyinkasid
Calcite | Level 5

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:
febyinkasid_0-1711095377566.png

 

Converted into date only in SAS with yymmdd format:

febyinkasid_1-1711095512444.pngfebyinkasid_2-1711095536361.png

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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.

febyinkasid
Calcite | Level 5
Thank You so much!
Tom
Super User Tom
Super User

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1047 views
  • 0 likes
  • 3 in conversation