topic Re: PROC IMPORT XLSX File and Specify Formats in SAS Programming

PROC IMPORT XLSX File and Specify Formats

djbateman — Fri, 02 Feb 2024 22:08:29 GMT

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;

Re: PROC IMPORT XLSX File and Specify Formats

Tom — Fri, 02 Feb 2024 22:15:00 GMT

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;

Re: PROC IMPORT XLSX File and Specify Formats

ballardw — Fri, 02 Feb 2024 22:28:33 GMT

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.

Re: PROC IMPORT XLSX File and Specify Formats

djbateman — Fri, 02 Feb 2024 23:05:48 GMT

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.

Re: PROC IMPORT XLSX File and Specify Formats

djbateman — Fri, 02 Feb 2024 23:07:17 GMT

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.