Hi all,
Does anyone know how to zip .xlsx files with SAS code?
Thanks.
F15
That's useless. xlsx files are already zipped.
I use 7ZIP sometimes and this approach, using X commands.
options noxwait noxsync;
data _null_;
*Path to winzip program;
zipexe='"C:\Program Files\7-Zip\7z.exe" a -tzip';
*Zip file name;
zipfile="C:\My Documents\Sample.zip";
*File to be zipped;
file="C:\Temp\Sample.txt";
*Full cmd line to be passed to command line. It embeds the quotes for paths with spaces;
cmd = zipexe ||' "'|| zipfile ||'" "'|| file ||'"' ;
*Place note in log;
putlog "NOTE-Processing command" cmd;
*Execute command;
call system(cmd);
run;
https://gist.github.com/statgeek/9602105
Or there's ODS PACKAGE as well:
https://blogs.sas.com/content/sasdummy/2014/01/28/create-zip-ods-package/
And FileName ZIP approach, similar to above:
https://blogs.sas.com/content/sasdummy/2016/03/04/add-files-to-a-zip-archive-with-filename-zip/
Why? XLSX files are already zipped, so it will not save space. Is it just to package multiple files together?
If you can use PIPE then use that to run system commands to build the ZIP file from the individual files.
But if that is disabled then you can use the ZIP engine and FCOPY() function to move XLSX files into a ZIP file.
Here is an example to export SASHELP.CLASS and SASHELP.CARS as XLSX and TXT files into a single ZIP file.
Note that PROC EXPORT to not smart enough to write directly to members in a zip file, so instead i had it write to separate physical files.
%let dir=%sysfunc(pathname(work));
filename xlsx1 "&dir/class.xlsx" recfm=n;
proc export data=sashelp.class outfile=xlsx1 dbms=xlsx replace;
run;
filename xlsx2 "&dir/cars.xlsx" recfm=n;
proc export data=sashelp.cars outfile=xlsx2 dbms=xlsx replace ;
run;
filename target1 zip "&dir/sample.zip" member='class.xlsx' recfm=n ;
filename target2 zip "&dir/sample.zip" member='cars.xlsx' recfm=n ;
data _null_;
rc=fcopy('xlsx1','target1');
put rc= ;
rc=fcopy('xlsx2','target2');
put rc= ;
run;
filename target zip "&dir/sample.zip" ;
data _null_;
set sashelp.class ;
file target('class.txt') dsd dlm='|' ;
put (_all_) (+0) ;
run;
data _null_;
set sashelp.cars ;
file target('cars.txt') dsd dlm='|' ;
put (_all_) (+0) ;
run;
So after running that I used the UNZIP command to check what I had created in the ZIP file.
data _null_;
infile "cd &dir ; unzip -Z sample.zip" pipe ;
input;
put _infile_;
run;
Archive: sample.zip Zip file size: 53487 bytes, number of entries: 4 -rwx------ 2.0 unx 6468 b- defN 17-Dec-08 14:56 class.xlsx -rwx------ 2.0 unx 37711 b- defN 17-Dec-08 14:56 cars.xlsx -rwx------ 2.0 unx 384 t- defN 17-Dec-08 14:56 class.txt -rwx------ 2.0 unx 35877 t- defN 17-Dec-08 14:56 cars.txt 4 files, 80440 bytes uncompressed, 53009 bytes compressed: 34.1%
Hi all,
Thanks for your suggestions. I'll try to work on a solution based on your ideas. If something comes up, I'll write again.
Thanks,
F15
Hi,
I have 20 xlsx files, every time i am zipping as seperate files (getting 20 zipped files,so zipping 20 times). if anyone knows best working solution,please let me know.
As already mentioned, zipping xlsx files makes no sense in terms of disk space reduction, as xlsx files are already zipped archives of xml data.
@Tom has given a nice example for using SAS to add files to a zip archive.
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.