Excel file increases more than expected

[deleted_user]

When I write the data out with the following code, the file size is just over 4MB's:



ods msoffice2k file='G:\mypath\report_test.xls'
options(sheet_name = 'report_test'
autofilter = 'all'
Frozen_Headers = '1'
doc = 'help') ;

proc print data = report_test noobs;
run;
ods msoffice2k close;




. . . when in E.G.(4.1), I highlight the data and use Send To -> Excel and do
a little formatting , when I'm done the file size is 905kb.

This is a huge difference in file size when using ODS as oppossed to just manually sending to Excel.

What's going on here and how can I resolve this programatically?

I'm trying to cut out any manual work for this so it can be scheduled to run daily.

Thanks.

[Cynthia_sas]

Hi!
When you use TAGSETS.MSOFFICE2K as the destination, ODS is creating an HTML file that can be opened with Excel (the kind of HTML that is being created conforms to the Microsoft HTML specification).

If you open your file 'G:\mypath\report_test.xls' with Notepad or any other text editor, you will see the HTML markup tags added around your data.

Consider this code:
[pre]
** A) use the Excel Libname Engine;
libname mywb "c:\temp\lib_data.xls" version=2002;

data mywb.class;
set sashelp.class;
run;

libname mywb clear;

** B);
** Use tagsets.msoffice2k to make an HTML file;
ods tagsets.msoffice2k file='c:\temp\mso_print.xls';
proc print data=sashelp.class;
run;
ods tagsets.msoffice2k close;

[/pre]

The file c:\temp\mso_print.xls is 27 KB in size (this is the HTML file) and the other file c:\temp\lib_data.xls is 12KB in size. So, yes, the HTML file is bigger than the true, binary Excel file. This may not always be the case, if I go into the lib_data.xls file and perform a lot of formatting or change fonts or cell colors, etc. (When I open SASHELP.CLASS in EG and then do a RMB-->Send To--> Excel, the file size is 14KB.)

In the above code, step A is creating a true, binary .XLS format file; while step B is creating an HTML file that Excel knows how to open.

THEN, if I do this:
[pre]
** C) Use tagsets.excelxp to make a Spreadsheet Markkup Language file;
** the options shown in your options statement ONLY work with the EXCELXP;
** tagset destination.;
ods tagsets.excelxp file='c:\temp\xp_print.xls'
options(sheet_name = 'report_test'
autofilter = 'all'
Frozen_Headers = '1'
doc = 'help') ;
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;
[/pre]

...what I get from step C is a Spreadsheet Markup Language XML file that is even more verbose than the HTML file. The c:\temp\xp_print.xls file is 36KB in size. What you have to decide is whether the trade off in size is worth the convenience of a scheduled job. If you don't care about any of the formatting that you get with msoffice2k then you can try these programmatically:

1) use the Libname Excel engine (as shown above) or use PROC EXPORT (an alternative to the Libname Engine);
[pre]
proc export data=sashelp.class
outfile="c:\temp\class.xls"
dbms=excel2000 replace;
run;
[/pre]


2) use ODS MSOFFICE2k with style=minimal
[pre]
ods tagsets.msoffice2k file='c:\temp\min.xls' style=minimal;
...more code...
ods tagsets.msoffice2k close;
[/pre]

If you find that you WANT the formatting (colors, fonts, frozen headers) of ExcelXP tagset destination then your choice is to see whether you can live with the file size as a tradeoff for getting the frozen headers, formatting, etc.

Good luck!
cynthia