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?
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
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.
Thanks for your reply Spacetime. I am using Excel 2003, so the files are xls, not xlsx.
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.
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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.