topic Re: How to compres a generated CSV file? in SAS Programming

How to compress a generated CSV file?

the_yeti — Mon, 14 May 2018 12:01:26 GMT

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?

Re: How to compres a generated CSV file?

Kurt_Bremser — Sat, 12 May 2018 18:54:50 GMT

Define _dataout with filename zip.

Re: How to compres a generated CSV file?

the_yeti — Sat, 12 May 2018 19:40:19 GMT

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;

Re: How to compres a generated CSV file?

Kurt_Bremser — Sat, 12 May 2018 19:48:07 GMT

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

then use

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

Re: How to compres a generated CSV file?

the_yeti — Sat, 12 May 2018 19:58:41 GMT

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:

Re: How to compres a generated CSV file?

Kurt_Bremser — Sat, 12 May 2018 20:04:12 GMT

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

Re: How to compres a generated CSV file?

the_yeti — Sat, 12 May 2018 20:43:39 GMT

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

Re: How to compres a generated CSV file?

ChrisHemedinger — Sat, 12 May 2018 20:48:16 GMT

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;

Re: How to compres a generated CSV file?

ChrisNZ — Sun, 13 May 2018 23:10:19 GMT

@Kurt_Bremser

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.

Re: How to compres a generated CSV file?

Kurt_Bremser — Mon, 14 May 2018 08:20:30 GMT

<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.

Re: How to compres a generated CSV file?

ChrisNZ — Mon, 14 May 2018 08:18:45 GMT

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

Re: How to compres a generated CSV file?

Kurt_Bremser — Mon, 14 May 2018 08:21:38 GMT

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

Re: How to compres a generated CSV file?

ChrisHemedinger — Mon, 14 May 2018 12:00:28 GMT

@ChrisNZ and @Kurt_Bremser - 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.

Re: How to compres a generated CSV file?

Kurt_Bremser — Mon, 14 May 2018 12:14:30 GMT

Just to expand on @ChrisHemedinger: gzip does not "need" multiple files in an archive, as that is usually handled on a UNIX by tar:

tar -cf - $HOME/path/* | gzip -c - > $HOME/path.gz

Re: How to compres a generated CSV file?

ChrisNZ — Mon, 14 May 2018 23:18:21 GMT

@Kurt_Bremser Late enough that I was home but not so late that I was asleep. 🙂

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. 🙂

@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;

Re: How to compres a generated CSV file?

the_yeti — Wed, 11 Jul 2018 15:33:05 GMT

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;

Re: How to compres a generated CSV file?

the_yeti — Wed, 11 Jul 2018 15:34:59 GMT

Thank you @Kurt_Bremser ! Adding the member name does the trick!