BookmarkSubscribeRSS Feed
Matt3
Quartz | Level 8

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.

4 REPLIES 4
Kurt_Bremser
Super User

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.

Matt3
Quartz | Level 8

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.

Kurt_Bremser
Super User

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.

Matt3
Quartz | Level 8
Ok, thank you for your help.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1134 views
  • 2 likes
  • 2 in conversation