topic Re: How to Compress a SAS dataset and Extract and Open in Windows in SAS Programming

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

lmtamina — Thu, 22 Oct 2020 01:16:20 GMT

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.

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

ChrisNZ — Thu, 22 Oct 2020 02:08:16 GMT

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;

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

ChrisNZ — Thu, 22 Oct 2020 02:08:52 GMT

Are the file sizes the same on both hosts?

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

Tom — Thu, 22 Oct 2020 02:33:02 GMT

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;

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

SASKiwi — Thu, 22 Oct 2020 02:52:05 GMT

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.

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

lmtamina — Thu, 22 Oct 2020 02:57:21 GMT

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.

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

lmtamina — Thu, 22 Oct 2020 03:00:41 GMT

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.

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

lmtamina — Thu, 22 Oct 2020 03:03:40 GMT

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.

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

lmtamina — Thu, 22 Oct 2020 03:20:41 GMT

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

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

SASKiwi — Thu, 22 Oct 2020 06:10:58 GMT

@lmtamina - That's the EG version, not the SAS version. Try this:

%put SAS release: &sysvlong;

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

ChrisNZ — Thu, 22 Oct 2020 08:15:38 GMT

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

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

ChrisNZ — Thu, 22 Oct 2020 08:17:05 GMT

> We are using SAS Enterprise Guide Version 7.15Hf8.

EG is just a dumb front-end client. It's the SAS version that matters.

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

Kurt_Bremser — Thu, 22 Oct 2020 09:27:47 GMT

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.

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

Tom — Thu, 22 Oct 2020 12:24:30 GMT

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.

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

lmtamina — Thu, 22 Oct 2020 12:31:46 GMT

Below is the log for the SAS version.

                                                    The SAS System                           08:26 Thursday, October 22, 2020

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='U:\999_CVOTM_SAS\SAS_Programs\zip_file_on_sas_server_before_ftp.egp';
6          %LET _CLIENTPROJECTPATHHOST='WKPC0WQLWR';
7          %LET _CLIENTPROJECTNAME='zip_file_on_sas_server_before_ftp.egp';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         
27         %put SAS release:&sysvlong;
SAS release:9.04.01M3P062415
28         
29         GOPTIONS NOACCESSIBLE;
30         %LET _CLIENTTASKLABEL=;
31         %LET _CLIENTPROCESSFLOWNAME=;
32         %LET _CLIENTPROJECTPATH=;
33         %LET _CLIENTPROJECTPATHHOST=;
34         %LET _CLIENTPROJECTNAME=;
35         %LET _SASPROGRAMFILE=;
36         %LET _SASPROGRAMFILEHOST=;
37         
38         ;*';*";*/;quit;run;
39         ODS _ALL_ CLOSE;
40         
41         
42         QUIT; RUN;
43

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

lmtamina — Thu, 22 Oct 2020 12:53:26 GMT

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

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

SASKiwi — Thu, 22 Oct 2020 18:49:42 GMT

You are using SAS 9.4 M3.which is about five years old. The latest is M7. A more recent release will likely work better for ODS. In my experience ODS uses a lot of computer resources and doesn't scale well for large datasets. This mirrors your experience of working OK for small datasets but not for large ones.

You might want to raise a track with SAS Tech Support to see if they can provide further guidance.

have you tired Re: How to Compress a SAS dataset and Extract and Open in Windows

ChrisNZ — Fri, 23 Oct 2020 03:34:30 GMT

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

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

lmtamina — Fri, 23 Oct 2020 21:13:43 GMT

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.