topic Re: Zipping xlsx files in SAS Programming

Zipping xlsx files

May15 — Fri, 08 Dec 2017 18:05:12 GMT

Hi all,

Does anyone know how to zip .xlsx files with SAS code?

Thanks.

F15

Re: Zipping xlsx files

Kurt_Bremser — Fri, 08 Dec 2017 18:07:02 GMT

That's useless. xlsx files are already zipped.

Re: Zipping xlsx files

Reeza — Fri, 08 Dec 2017 18:10:57 GMT

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/

Re: Zipping xlsx files

Tom — Fri, 08 Dec 2017 20:02:26 GMT

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%

Re: Zipping xlsx files

May15 — Sun, 10 Dec 2017 13:30:01 GMT

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

Re: Zipping xlsx files

rajeshm — Thu, 10 Dec 2020 10:17:44 GMT

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.

Re: Zipping xlsx files

Kurt_Bremser — Thu, 10 Dec 2020 11:01:13 GMT

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.