Since xlsx files are already ZIP-compressed archives of XML data, you won't get much, if any, further compression.
Since CSV files do not have the overhead of the XML tags (which is considerable), they will end up being much smaller than XLSX files, and have the advantage of being in "one piece" (instead of the 38 sheets you would need).
Next, because of the structure of XLSX, with every new sheet added, the whole file will have to be uncompressed, the new XML data created, and the resulting directory tree compressed again. Performancewise, that's deadly.
Let's do some tests (run in University Edition on a 2012 MacBook Pro):
Create fake data with > 800000 obs:
data work.cars;
set sashelp.cars;
do i = 1 to 2000;
output;
end;
run;
Export to a single sheet in a XLSX:
libname myex xlsx '/folders/myfolders/test.xlsx';
proc copy in=work out=myex;
select cars;
run;
libname myex clear;
Create a zip file in a data step:
filename outzip zip '/folders/myfolders/test.zip';
data _null_;
set work.cars;
file outzip(cars);
put
cylinders drivetrain enginesize horsepower invoice length
make model mpg_city mpg_highway msrp origin type weight wheelbase
;
run;
filename outzip clear;
See the combined log:
73 data work.cars;
74 set sashelp.cars;
75 do i = 1 to 2000;
76 output;
77 end;
78 run;
NOTE: There were 428 observations read from the data set SASHELP.CARS.
NOTE: The data set WORK.CARS has 856000 observations and 16 variables.
NOTE: Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
real time 0.87 seconds
cpu time 0.80 seconds
79
80 libname myex xlsx '/folders/myfolders/test.xlsx';
NOTE: Libref MYEX was successfully assigned as follows:
Engine: XLSX
Physical Name: /folders/myfolders/test.xlsx
81
82 proc copy in=work out=myex;
83 select cars;
84 run;
NOTE: Copying WORK.CARS to MYEX.CARS (memtype=DATA).
NOTE: There were 856000 observations read from the data set WORK.CARS.
NOTE: The data set MYEX.CARS has 856000 observations and 16 variables.
NOTE: The export data set has 856000 observations and 16 variables.
NOTE: Verwendet wurde: PROZEDUR COPY - (Gesamtverarbeitungszeit):
real time 44.76 seconds
cpu time 44.20 seconds
85
86 libname myex clear;
NOTE: Libref MYEX has been deassigned.
87
88 filename outzip zip '/folders/myfolders/test.zip';
89
90 data _null_;
91 set work.cars;
92 file outzip(cars.csv) dlm="," dsd;
93 put
94 cylinders drivetrain enginesize horsepower invoice length
95 make model mpg_city mpg_highway msrp origin type weight wheelbase
96 ;
97 run;
NOTE: The file library OUTZIP is:
Directory=/folders/myfolders/test.zip
NOTE: The file OUTZIP(cars.csv) is:
Filename=/folders/myfolders/test.zip,
Member Name=cars.csv
NOTE: A total of 856000 records were written to the file library OUTZIP.
The minimum record length was 68.
The maximum record length was 111.
NOTE: 856000 records were written to the file OUTZIP(cars.csv).
The minimum record length was 68.
The maximum record length was 111.
NOTE: There were 856000 observations read from the data set WORK.CARS.
NOTE: Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
real time 2.93 seconds
cpu time 2.92 seconds
98
99 filename outzip clear;
Note that creating the XLSX takes about 15 times as long as writing the zip file.
The XLSX is 42.3 MB, the zip file a meager 277K !
Now let's add another sheet to the XLSX:
libname myex xlsx '/folders/myfolders/test.xlsx';
data myex.cars2;
set work.cars;
run;
libname myex clear;
and the log from that:
73 libname myex xlsx '/folders/myfolders/test.xlsx';
NOTE: Libref MYEX was successfully assigned as follows:
Engine: XLSX
Physical Name: /folders/myfolders/test.xlsx
74
75 data myex.cars2;
76 set work.cars;
77 run;
NOTE: There were 856000 observations read from the data set WORK.CARS.
NOTE: The data set MYEX.cars2 has 856000 observations and 16 variables.
NOTE: The export data set has 856000 observations and 16 variables.
NOTE: Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
real time 56.83 seconds
cpu time 54.71 seconds
78
79 libname myex clear;
NOTE: Libref MYEX has been deassigned.
As expected, the time for further sheets increases.
Bottom line: do not use Excel files for data transfer. Text files and using a standard compression tool beats them hands down.
