topic Re: proc import without xslx formats in SAS Enterprise Guide

proc import without xslx formats

Matt3 — Wed, 19 Jul 2017 06:55:41 GMT

Hello, Is there any way to import xlsx file without excel formats?

mixed option is not working. I ve got datatime in excel file in yyyy-mm-dd format, procedure import this column in the same format cutting time part. I would appreciate any help. Thanks.

Re: proc import without xslx formats

Kurt_Bremser — Wed, 19 Jul 2017 07:02:27 GMT

Export to CSV.

Inspect the CSV to see in which format Excel wrote the values today.

Write a data step that converts these values to the wanted variable types, and set formats as wanted.

proc import has to make guesses, and is hard to impossible to control.

Re: proc import without xslx formats

Matt3 — Wed, 19 Jul 2017 07:19:25 GMT

The problem is, I do receive reports on regular basis, it can`t be changed, and I want to automize the import process. I can switch into another procedure or data step or java object in sas but I can not change xlsx format.

Re: proc import without xslx formats

Kurt_Bremser — Wed, 19 Jul 2017 07:26:44 GMT

LibreOffice (OpenOffice) can be used to convert office files from the commandline, so that part is easy to automate. I can only repeat that the Excel file format is absolutely NOT SUITED for the transfer of tabular data for business intelligence. No control.

Searching around here on the SAS communities, you will find that statement repeated over and over again by people with experience.

Excel itself doesn't lend itself well to automating, documenting and auditing.

From my experience:

Only about 1% of our jobs concern imports of data that initially came from Excel (or were at one place passed through Excel), but they cause about 10% of mainentance effort (searching and correcting errors). It's come so far that our auditors have explicitly forbidden the use of Excel for BI tasks. It may only be used as a data-entry tool for small data (like lookup tables). No Excel logic AT ALL.

Re: proc import without xslx formats

Matt3 — Wed, 19 Jul 2017 07:45:22 GMT

Ok, thank you for your help.