PROC IMPORT XLSX File and Specify Formats

[djbateman]

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:

Data in SAS after import:

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;

[Tom · Accepted solution]

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;

[djbateman]

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]

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]

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.