How to Compress a SAS dataset and Extract and Open in Windows

[lmtamina]

Hi All,

I am trying to export  a SAS dataset (approx. 12Gb) that I already converted to a CSV file format . I compressed the file so I can FTP to our vendor . Our FTP will only transmit if the file size is less the 5GB. I compressed the file in SAS (reduced to approx.4GB)  and use COPY FILES option to export to Windows. However, when I tried to extract and open the zip file I copied to Windows , I cannot open and it is giving me an error: "Windows cannot complete extraction . the destination file could not be created". I used to do this routin with smaller file size and was able to open the compressed file. Greatly appreciate your assistance . Thank you as always!

Step 1 . Convert to csv

proc export data= Claimsfile outfile="%sysfunc(pathname(WORK))/Claimsfile.csv" dbms=csv replace;run;

Step 2: Compress CSV file

ods package(zipout) open nopf;
ods package(zipout)
	add file="%sysfunc(pathname(WORK))/claimsfile.csv";
ods package(zipout) 
	publish archive properties 
	(archive_name="claimsfile.zip"
	archive_path="%sysfunc(pathname(WORK))");
ods package(zipout) close;

Step : 3

 Use Task->Data-Copy Files  to export from Unix to Windows.

[ChrisNZ]

The error seems to pertain to the file created. Try this:

filename CSV "%sysfunc(pathname(WORK))/claimsfile.csv";

proc export data=SASHELP.CLASS outfile=CSV dbms=csv replace;run;

ods package(zipout) open nopf;
ods package(zipout) add file=CSV path="/";
ods package(zipout) 
	publish archive properties 
	(archive_name="claimsfile.zip"
	archive_path="%sysfunc(pathname(WORK))");
ods package(zipout) close;

[lmtamina]

Thank you . I tried this solution and was able to unzip the file. This solution work . Just wondering why my original code did not work when I used this before. 

Also. I am trying to send 18 of these SAS datasets with approx. 12 GB each uncompressed, and approx. 4GB compressed  in csv format. IS there a more efficient way to convert to csv , then compress and export from SAS to Windows. I have been trying to this steps for each file ( i.e convert to csv , compress the file, export to Windows) one by one. Very manual and time consuming. I only know basic SAS programming and not a heavy programmer. Appreciate any advice.

Thank you again for this solution but would love how to automate processing the remaining 17 files. Appreciate your expertise.

[ChrisNZ]

>  Just wondering why my original code did not work when I used this before. 

My guess is that the slash was used by Windows, and that's an illegal file name character under Windows.

[lmtamina]

1) I tried your recommendation above and use the SAS ZIP option above. I tried opening the ZIP file in WIndows and now getting another error :

"1 Interrupted Action
An unexpected error is keeping from copying the file. If you continue to receive this error, you can use the error code to search for help with this problem.
Error 0x80004005: Unspecified Error"

What is interesting is I notice if the files size is smaller (ex.: less than 1 GB) there is no error and able to extract the zip in WIndows. However, if the file is approx. 4GB , it gives out this error.

We are now trying to ZIP directly in WIndows .We initially used zip in SAS as we found it faster than ZIP in Windows.

2) I am going through Windows to FTP as I do not have the expertise to use SFTP in SAS.

[ChrisNZ]

Are the file sizes the same on both hosts?

[Tom]

Make sure to copy the file zip file as BINARY.

Is the issue that the file it too large for your target machine?

Why not just use the ZIP engine?  I would use the GZIP option if there is just one file to send.

filename csv zip "%sysfunc(pathname(WORK))/Claimsfile.csv.gz" gzip lrecl=1000000 ;

proc export data= Claimsfile outfile=csv dbms=csv replace;
run;

Then it is easy to read on the other side.  Example:

filename csv zip "c:\downloads\Claimsfile.csv.gz" gzip lrecl=1000000 ;
data claimsfile ;
  infile csv dsd truncover firstobs=2;
  input var1 .... ;
run;

[lmtamina]

 I am trying to send 18 of these SAS datasets with approx. 12 GB each uncompressed, and approx. 4GB compressed  in csv format. Is there a more efficient way to convert to csv , then compress and export from SAS to Windows. I have been trying to do these steps for each file ( i.e. convert to csv , compress the file, export to Windows) one by one. Very manual and time consuming. I only know basic SAS programming and not a heavy programmer. Appreciate any advice. Appreciate a solution  that would automate processing the remaining 17 files. Appreciate your expertise.

[lmtamina]

@Tom  I tried the GZIP option and it appears my SAS EG version does not support this. Also, I am tryin gto send 18 of these datasets. Appreciate a more efficient soltuion to send the remaing 17 files . Doin git one by one (i.e. converting to csv, zipping and exporting to Windows) is very time consuming . Apprecaite your expertise. Thank you.

        ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         filename csv zip "%sysfunc(pathname(WORK))/MedClaims_2017_6.csv.gz" gzip lrecl=1000000 ;
                                                                               ____
                                                                               23
ERROR: Error in the FILENAME statement.
ERROR 23-2: Invalid option name gzip.

27         
28         GOPTIONS NOACCESSIBLE;
29         %LET _CLIENTTASKLABEL=;
30         %LET _CLIENTPROCESSFLOWNAME=;
31         %LET _CLIENTPROJECTPATH=;
32         %LET _CLIENTPROJECTPATHHOST=;
33         %LET _CLIENTPROJECTNAME=;
34         %LET _SASPROGRAMFILE=;
35         %LET _SASPROGRAMFILEHOST=;
36         
37         ;*';*";*/;quit;run;
38         ODS _ALL_ CLOSE;
39         
40         
41         QUIT; RUN;
42         

usong the gzip command , and I am getting an error.

[Kurt_Bremser]

Please run this

%put &=sysvlong;
%put &=syshostinfolong;

and post the log; I suspect your SAS runs on Windows, where the gzip option is not available.

[Tom]

You seem to be at some intermediate SAS version where ZIP engine works but the GZIP enhancement has not been added.  So just adjust the syntax to deal with that.

filename csv zip "%sysfunc(pathname(WORK))/Download_2017_6.zip" 
   member="Claims.csv"  lrecl=1000000 
;

Now if you want you could store all 18 CSV files in the same ZIP file by just changing the MEMBER= option.

[lmtamina]

@
Accept as Solution









Highlighted











Unforunately I cannot store all 18 files into 1 zip as each has file is already approx 4Gb(compressed). We are seniding these files to our vendor via SFTP and it will only transmit file with less than 5GB . I was just hoping there is a more efficient way to convert to csv, zip the csv, and copy to Windows.

[ChrisNZ]

> I was just hoping there is a more efficient way

1. Have you tried this as suggested? 

filename CSV zip "%sysfunc(pathname(WORK))/file.zip" member="file.csv" lrecl=1000000; 
proc export data=SASHELP.CLASS dbms=csv outfile=CSV replace; run;

2. SAS can send to SFTP, why do you go through Windows?

3. You could automate the transfer to Windows if you had a samba drive on the unix machine. Or create a task on EG.

Sadly SAS has killed its desktop client by pushing a dumb and free EG client to all its customers, so that's the only 2 options you have.

[SASKiwi]

What SAS 9.4 maintenance release are you on? ODS has matured a lot over the 9.4 maintenance releases. If you are using an earlier one like M1 - M3 that could cause issues. For the size of file @Tom 's FILENAME alternative is a better option.

[lmtamina]

We are using SAS Enterprise Guide Version 7.15Hf8. I used to be able to use my original code before and no problem. It's been quite awhile though when I  used it.