Import CSV file in zip format into sas server

[wtien196838]

To SAS community users:

I try to import a csv file in zip format to sas server, but unsuccessfuly.

The source code is:
----------------------------------
FILENAME inzip SASZIPAM "\..path..\aaa bbb.zip" member="aaa bbb.csv" ;

PROC IMPORT datafile=inzip  out=work.test dbms=csv replace;
            getnames=yes;
     RUN;

/* comment: there is a blank space in the filename as user defined, the zip file has only one member */
---------------------------------
The log  is :

 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in

update mode. Temporary parameter values will be saved to
 WORK.PARMS.PARMS.SLIST.
 ERROR: Member aaa bbb.CSV does not exist.
 ERROR: Import unsuccessful.  See SAS Log for details.
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.04 seconds
       cpu time            0.03 seconds

Would you please take a look and suggest me a solution.

Thanks in advance.

Best Regards,

William

[Reeza]

What version of SAS do you have?

[Reeza]

Also, I'm not sure that's the correct code. The proc import example I found seems to imply you have to copy the dataset out first.

http://blogs.sas.com/content/sasdummy/2015/05/11/using-filename-zip-to-unzip-and-read-data-files-in-...

The documentation implies direct access but all show a data step import instead of proc import. 

Try using a datastep import instead of proc import.

https://support.sas.com/documentation/cdl/en/lestmtsref/68024/HTML/default/viewer.htm#n1dn0f61yfyzto...

Also, this method requires SAS 9.4, so if you have a lower version you need to use a different method to import the data. 

[wtien196838]

Hi Reeza

  My sas version is 9.3.

  I try to use input codes but it still failed. Here is the codes and log:

FILENAME inzip saszipam "\..path..\aaa bbb.zip" ;

 DATA WORK.TEST ;
  INFILE inzip(aaa bbb.csv) dlm=',' FIRSTOBS=2 missover dsd;
  INPUT
     var1       $
     var2       $
     .......
    ;
run;


NOTE: The infile library INZIP is:
      Stream=\..path..\aaa bbb.zip

NOTE: The infile INZIP(aaa bbb.csv) is:
      File Name=aaa bbb.csv,
      Compressed Size=80585617,
      Uncompressed Size=1649850367,
      Compression Level=-1,Clear Text=No

ERROR: Invalid data length.
FATAL: Unrecoverable I/O error detected in the execution of the DATA step program.  Aborted during the EXECUTION phase.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete.  When this step was stopped there were 0 observations and 80 variables.
WARNING: Data set WORK.TEST was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

/*** I have problem to find the record length of this zip even I know all variable names (user supplied) ***/

Thanks for your help.

[Reeza]

You'll note in my answer, I do specify that reading from a ZIP file using this method requires SAS 9.4. If you have SAS 9.3 this code won't work. You'll need to extract the zip file and then read the .csv file. You can use X or %sysexec commands to automate the extraction of the file. There are examples of that posted on the forum or on lexjansen.com

[Tom]

PROC IMPORT is a little brain dead and does not work well with some infile options that a normal data step does not have any trouble with.  Can you just write your own data step to read the file instead of trying to use PROC IMPORT?

Or use a data step to read the file to a physical file (or part of the file if it is really large).  Then you could use PROC IMPORT. If you are running interactively you can recall the generated data step and modify it to work.

FILENAME inzip SASZIPAM "\..path..\aaa bbb.zip" member="aaa bbb.csv" ;
FILENAME copy TEMP;
data _null_;
   infile inzip ;
   file copy ;
   input;
   put _infile_;
run;

Not sure why you are using SASZIPAM engine instead of the ZIP engine.

FILENAME ziplib zip "\..path..\aaa bbb.zip";
FILENAME copy TEMP;
data _null_;
   infile ziplib("aaa bbb.csv") ;
   file copy ;
   input;
   put _infile_;
run;

[wtien196838]

Tom

The first method generated this error:

ERROR: Member aaa bbb.CSV does not exist.
NOTE: The file COPY is:
      Filename=G:\SAS Temporary Files\.....\#LN00036,,
      RECFM=V,LRECL=256,File Size (bytes)=0,
      Last Modified=08Jan2016:11:29:28,
      Create Time=08Jan2016:11:29:28

NOTE: 0 records were written to the file COPY.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

The second method generated error:

ERROR: Invalid device type.
ERROR: Error in the FILENAME statement.

/*** It looks like my system support "SASZIPAM" instead of "ZIP"  **   My system is SAS 9.3   ***/

[Reeza]

http://support.sas.com/kb/31/244.html

Note - if the file wasn't created with WINZIP you get an IO error.

[Reeza]

Assuming you have access to 7-zip here's sample code to use it from SAS to unzip your file and then you can read the text file as you normally would in SAS.

x '"C:\Program Files (x86)\7-Zip\7z.exe" e "C:\_localdata\delete.zip" -o"C:\_localdata\"';

[wtien196838 · Accepted solution]

Thanks Reeza.

I asked the tech support to do it.