BookmarkSubscribeRSS Feed
May15
Obsidian | Level 7

Hi all,

 

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

 

 

Thanks.

 

F15

6 REPLIES 6
Reeza
Super User

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/

Tom
Super User Tom
Super User

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%

 

May15
Obsidian | Level 7

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

rajeshm
Quartz | Level 8

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.

Kurt_Bremser
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2360 views
  • 0 likes
  • 5 in conversation