Zipping xlsx files

Reply
Contributor
Posts: 37

Zipping xlsx files

Hi all,

 

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

 

 

Thanks.

 

F15

Super User
Posts: 8,590

Re: Zipping xlsx files

That's useless. xlsx files are already zipped.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 21,546

Re: Zipping xlsx files

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/

Super User
Super User
Posts: 7,401

Re: Zipping xlsx files

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%

 

Contributor
Posts: 37

Re: Zipping xlsx files

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

Ask a Question
Discussion stats
  • 4 replies
  • 126 views
  • 0 likes
  • 4 in conversation