How to compress a generated CSV file?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

How to compress a generated CSV file?

[ Edited ]

I'm generating a CSV file from a SAS dataset, and I'd like to compress it to zip before downloading it.

 

proc export data=sashelp.cars
  outfile=_dataout
  dbms=csv replace;
run;

%let _DATAOUT_MIME_TYPE=text/csv;
%let _DATAOUT_NAME=cars.csv;

 

How can I do that?

 


Accepted Solutions
Solution
Wednesday
Super User
Posts: 10,217

Re: How to compres a generated CSV file?

[ Edited ]

<spock>Fascinating</spock>. 7-zip can open the file, while Windows Explorer shows it empty. UNIX unzip (AIX) comes back with

Archive:  cars.csv.zip
mapname:  conversion of  failed

unzip -l shows

Archive:  cars.csv.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
    37713  05-14-2018 09:34   
---------                     -------
    37713                     1 file

so there seems to be an unnamed entry that confuses some zip programs.

 

This research led me to reread the documentation of filename zip (following Maxim 1), and I came up with this solution:

filename _dataout zip "$HOME/sascommunity/cars.csv.zip" member="cars.csv";

proc export data=sashelp.cars
  outfile=_dataout
  dbms=csv replace;
run;

The unzip -l now shows

  Length      Date    Time    Name
---------  ---------- -----   ----
    37713  05-14-2018 09:50   cars.csv
---------                     -------
    37713                     1 file

and UNIX unzip works. Similarly, Windows Explorer now shows the file, and I guess WinZip will also work.

 

BTW filename zip with the gzip option, as suggested by @ChrisHemedinger, works perfectly without any additional options.

 

PS all my tests run on SAS 9.4M5 and AIX, Windows tests on Windows 7.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,217

Re: How to compres a generated CSV file?

Define _dataout with filename zip.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 17

Re: How to compres a generated CSV file?

Posted in reply to KurtBremser

Thank you Kurt.

 

Could you point me to an example?

 

I tried a few different combinations, but no luck...

 

proc export data=sashelp.cars
  outfile=test.zip zip
  dbms=csv replace;
run;
proc export data=sashelp.cars
  outfile=test zip
  dbms=csv replace;
run;
proc export data=sashelp.cars
  outfile='test.zip' zip
  dbms=csv replace;
run;
Super User
Posts: 10,217

Re: How to compres a generated CSV file?

filename _dataout zip "/path/name.zip";

then use

proc export data=sashelp.cars
  outfile=_dataout
  dbms=csv replace;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 17

Re: How to compres a generated CSV file?

Posted in reply to KurtBremser

Thank you Kurt.

 

That code seems to generate some sort of compressed file, instead of the plain text CSV, however it doesn't seem to be in the ZIP format... It's some other compression I'm afraid.

 

Is there any hint I can use in the `filename` instruction to tell it o use LZMA, etc?

 

This is the beginning of the file:

 

compressed-file.png

Super User
Posts: 10,217

Re: How to compres a generated CSV file?

filename zip is compatible with WinZip, and since 9.4M5 also supports the gzip compression.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 17

Re: How to compres a generated CSV file?

Posted in reply to KurtBremser

Hmm I think something odd is happening. The file cannot be opened by Windows. It shows an empty folder.

Community Manager
Posts: 3,424

Re: How to compres a generated CSV file?

Running on a 9.4 M5 environment?  Try GZIP.

 

filename _dataout zip "/path/cars.csv.gz" GZIP;

proc export data=sashelp.cars
  outfile=_dataout
  dbms=csv replace;
run;
Occasional Contributor
Posts: 17

Re: How to compres a generated CSV file?

Posted in reply to ChrisHemedinger

Thank you Chris.

 

I think my environment might not support GZIP for some reason... I get the following error message:

 

ERROR: Error in the FILENAME statement.
ERROR 23-2: Invalid option name GZIP.
ERROR: Insufficient authorization to access /sso/biconfig/940/Lev1/SASApp/_DATAOUT.

 

The code I'm running:

 

filename _dataout zip "/path/that/I/know/I/can/write/to/cars.csv.gz" GZIP;

proc export data=sashelp.cars
  outfile=_dataout
  dbms=csv replace;
run;

 

 

 
PROC Star
Posts: 2,342

Re: How to compres a generated CSV file?

Posted in reply to KurtBremser

@KurtBremser 

Your code generates a 11-kB file (CSV is 38kB) that when opened with Winzip on Win7 

yields a warning "Cannot open file xxx.zip. It does not appear to be a valid archive"

with error message : Error: invalid central directory entry (No. 1) encountered.

 

My version of Winzip seems to be old though. Version 17.5 (10652) 64-bit.

It may be the version that came with Win7. 

 

 

Solution
Wednesday
Super User
Posts: 10,217

Re: How to compres a generated CSV file?

[ Edited ]

<spock>Fascinating</spock>. 7-zip can open the file, while Windows Explorer shows it empty. UNIX unzip (AIX) comes back with

Archive:  cars.csv.zip
mapname:  conversion of  failed

unzip -l shows

Archive:  cars.csv.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
    37713  05-14-2018 09:34   
---------                     -------
    37713                     1 file

so there seems to be an unnamed entry that confuses some zip programs.

 

This research led me to reread the documentation of filename zip (following Maxim 1), and I came up with this solution:

filename _dataout zip "$HOME/sascommunity/cars.csv.zip" member="cars.csv";

proc export data=sashelp.cars
  outfile=_dataout
  dbms=csv replace;
run;

The unzip -l now shows

  Length      Date    Time    Name
---------  ---------- -----   ----
    37713  05-14-2018 09:50   cars.csv
---------                     -------
    37713                     1 file

and UNIX unzip works. Similarly, Windows Explorer now shows the file, and I guess WinZip will also work.

 

BTW filename zip with the gzip option, as suggested by @ChrisHemedinger, works perfectly without any additional options.

 

PS all my tests run on SAS 9.4M5 and AIX, Windows tests on Windows 7.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 2,342

Re: How to compres a generated CSV file?

Posted in reply to KurtBremser

@KurtBremser Colour me impressed. Well done! I wonder whether this behaviour is expected.

Super User
Posts: 10,217

Re: How to compres a generated CSV file?

Still up? How late is it now next to "down under"?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 2,342

Re: How to compres a generated CSV file?

Posted in reply to KurtBremser

@KurtBremser Late enough that I was home but not so late that I was asleep. Smiley Happy

 

Otherwise: After doing some (belated) reading, it seems a member name is required. Either in the form you mentioned or like this:

 

 

filename FOO zip 'U:\directory1\testzip.zip';
data _null_;
  file FOO(shoes);

 

There we go. My learning for the day. Smiley Happy

 

@ChrisHemedinger even details how to send a SAS data set in a ZIP file here though the new fcopy() function makes this easier.

filename IN      "&path\test.sas7bdat" recfm=n;
filename OUT zip "&path\test.zip" member='test.sas7bdat' ;
%let rc=%sysfunc(fcopy(IN,OUT)); %put &=rc;

 

 

Community Manager
Posts: 3,424

Re: How to compres a generated CSV file?

@ChrisNZ and @KurtBremser - expected or not, I think it's good practice (maybe essential) to explicitly name the "member" files for the ZIP method, since there can be multiple files in the archive.  For GZIP, that's not necessary (or supported) since it's just a single compressed file, not an archive of several files.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 243 views
  • 9 likes
  • 4 in conversation