BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

I am trying to import an xslx data file.  There are several fields that have date/time values, but when SAS imports, they are converted to just the date value.  There are no blank values or other data in the columns that would encourage SAS to make up a different format.  I cannot figure out how to force it to read the full date and time.  I have played around with DBDSOPTS, but I don't think I fully understand how that works.  My log is coming out clean.  I have attached a sample file for you to play with.  Can someone help?

 

Data in Excel:

djbateman_1-1706911547033.png

Data in SAS after import:

djbateman_0-1706911524337.png

 

Here is the code I am attempting to use:

 

filename testrpt "C:\Test Report.xlsx";
proc import out=have datafile=testrpt dbms=excelcs replace;
     range="Export$";
     scantext=yes;
     usedate=yes;
     scantime=yes;
run;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Is the file accessible on the machine were SAS is running so that you can test what happens if you use XLSX as the DBMS setting instead of EXCELCS?

proc import dbms=xlsx file='c:\downloads\Test Report.xlsx' out=test replace ;
run;

proc contents varnum;
run;

Tom_0-1706912093017.png

 

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

Is the file accessible on the machine were SAS is running so that you can test what happens if you use XLSX as the DBMS setting instead of EXCELCS?

proc import dbms=xlsx file='c:\downloads\Test Report.xlsx' out=test replace ;
run;

proc contents varnum;
run;

Tom_0-1706912093017.png

 

djbateman
Lapis Lazuli | Level 10
This worked wonderfully! I tried playing around with different DBMS sources, but I don't think I had the right syntax. Thanks for your help.
ballardw
Super User

If you want to control an INFORMAT (not form when reading data) then save the XLSX to CSV and write a data step to use the informat that you want. Assign a display format that your like.

 

I strongly suggest instead of showing pictures, where we have absolutely no idea what actual values you have, that at least provide the output from Contents to describe the SAS data set created.

 

If you have a datetime value and display it with a DATETIME9. format it will look like that. So it may have been read as desired but the format to display it is just not wide enough.

djbateman
Lapis Lazuli | Level 10

I considered converting to CSV, but I will have to run this daily from updated files, and I need to be able to read in the data as it comes in rather than saving as csv every single day.  Just not sustainable.  But I will be sure to share the dataset contents next time.  I knew the imported dates were saved as DATE9., but didn't think to share that beyond my code and source file.  Thanks or the feedback.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

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

Browse our catalog!

Discussion stats
  • 4 replies
  • 1775 views
  • 1 like
  • 3 in conversation