03-24-2015 02:35 PM
I am seeking your help to reduce the output file size (xls) from proc report by using ODS EXCELXP.TAGSETS option.
I have created the 1540 reports in mainframe and each file size is around 10 MB so it is taking too much time to transfer the file from mainframe to windows location. I have also tried the style=minimal option but no luck. One noteable thing is that if i export the datasets the file size has been reduced to 333KB from 10 MB but it does not have asthetics look.
So please can you help me on this issue.
03-24-2015 03:45 PM
That's XML for you. One of the most verbose methods of interchanging data around. The files created by tagsets.excelxp are actually XML that Excel can open. They are just text files at heart so if you have a compatible compression program that will make a ZIP file or similar then the transferring the compressed file to a windows machine would likely be quicker. If you search there are some VB scripts referenced on this forum for opening these files and saving them as actual XLS or XLSX format files once you have them where Excel can read them.
The ideal would be to create a true XLS or XLSX file directly from SAS with the appropriate PC Files approach.
03-25-2015 10:31 AM
TBH my first question would be why the need for 1540 Excel files with x number of observations. ballardw's idea of using .xls (which is M$ old binary proprietary format) will indeed be a lot smaller. However thats still a large amount of Excel files. Is this some kind of data transfer or a review output? If its data transfer then there are better methods (using CSV will drastically shrink your file size also and still be openable in Excel - though again without the formatting). If its output though what you could do is go down the route of CSV, i.e. small data file, then have an Excel document with some built in VBA functions which load and present the data.
The only other suggestion I can offer is to build the XLSX file yourself. Within the XLSX (rename it zip and have a look), is a folder structure and some files. One of the files relates to unique values. This is their attempt to minimise the size. So you could build that yourself.