topic Re: Import CSV file in zip format into sas server in SAS Programming

Import CSV file in zip format into sas server

wtien196838 — Fri, 08 Jan 2016 01:09:57 GMT

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

Re: Import CSV file in zip format into sas server

Reeza — Fri, 08 Jan 2016 03:43:37 GMT

What version of SAS do you have?

Re: Import CSV file in zip format into sas server

Reeza — Fri, 08 Jan 2016 03:50:35 GMT

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-sas/

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#n1dn0f61yfyzton1l2ngsa1clllr.htm

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.

Re: Import CSV file in zip format into sas server

wtien196838 — Fri, 08 Jan 2016 14:24:02 GMT

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.

Re: Import CSV file in zip format into sas server

Reeza — Fri, 08 Jan 2016 15:21:52 GMT

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

Re: Import CSV file in zip format into sas server

Tom — Fri, 08 Jan 2016 15:29:20 GMT

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;

Re: Import CSV file in zip format into sas server

wtien196838 — Fri, 08 Jan 2016 16:41:57 GMT

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 ***/

Re: Import CSV file in zip format into sas server

Reeza — Fri, 08 Jan 2016 17:01:13 GMT

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

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

Re: Import CSV file in zip format into sas server

Reeza — Fri, 08 Jan 2016 17:31:48 GMT

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\"';

Re: Import CSV file in zip format into sas server

wtien196838 — Thu, 07 Apr 2016 22:40:56 GMT

Thanks Reeza.

I asked the tech support to do it.