BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Keith
Obsidian | Level 7

I'm transferring a SAS dataset to Excel using the ODS ExcelXP tagset.  The spreadsheet created is 41MB, however if I open it, save it without making any changes, then close it, the file size reduces to 34MB.  I assume this is due to some form of compression, but does anyone know if this can be done automatically as part of the ODS process?

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

Excelxp tagset ODS outputs 'XML' files (sometime faked with xls name), not 'true' 'xls' files. XML is some sort of 'formated' text file that both human and machine can read. While XLS is binary file, which is much more compact. So when you resave it, its structure may have been modified to true XLS file. That is why there is a need to shrink XML file using third party software or VBA scripts. I usually find it can shrink to 1/5 or more after conversion.

Haikuo

View solution in original post

6 REPLIES 6
Spacetime
Obsidian | Level 7

I don't think you can do this.  What you are describing seems to occur when you save a .xls to a .xlsx in Excel.  I don't believe the ExcelXP tagset can write .xlsx files directly.

You may try to use a different tagset which can write .xlsx files if one exists.  Or you could write a VBA macro after to go and convert all your sheet to xlsx.

Sorry if this was off the mark, but this is my best guess to what is happening.

Keith
Obsidian | Level 7

Thanks for your reply Spacetime.  I am using Excel 2003, so the files are xls, not xlsx.

Spacetime
Obsidian | Level 7

Yeah no problem, now I see from the other responses it's due to the xml structure, and not the xlsx.  I would always wonder why I would save them as xlsx and they would shrink, but the same thing would have happened with just straight xls.  This is helpful to solidify my understanding here.

Haikuo
Onyx | Level 15

Excelxp tagset ODS outputs 'XML' files (sometime faked with xls name), not 'true' 'xls' files. XML is some sort of 'formated' text file that both human and machine can read. While XLS is binary file, which is much more compact. So when you resave it, its structure may have been modified to true XLS file. That is why there is a need to shrink XML file using third party software or VBA scripts. I usually find it can shrink to 1/5 or more after conversion.

Haikuo

Keith
Obsidian | Level 7

Thanks Hai.kuo, I guessed it may be something like that.  I suppose it's similar to the old days of using ODS HTML to create an .xls file.  With that method you had to click 'Save As' in Excel and choose Excel Workbook as the file type.

ballardw
Super User

And since this is a result of the "File Save As" in Excel it isn't possible directly with ODS. A work around using DDE is possible but is fragile as Excel version, file types and command syntax changes. It was worth it to me as I had hundreds of files to do the "File Save As" to.

See https://communities.sas.com/thread/34669

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!

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.

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
  • 6 replies
  • 3168 views
  • 0 likes
  • 4 in conversation