topic Re: Importing xls directly from Zip files in SAS Procedures

Importing xls directly from Zip files

blue_lion — Mon, 22 Sep 2014 20:55:29 GMT

I am having trouble importing xls files contained in Zip files and below is the code.

Below code up to downloading the Zip file should be fine as I was following instructions from SAS blog and I was able to import txt files successfully.

but I'm not sure how to do the same with xls files contained in a Zip, so if anyone knows how to do this, I really appreciate your help.

The error msg I got is below and I think I need to specify xls file name contained in the Zip because I only specified zip file location, but I could not find similar example online and I'm not sure how to specify this.

ERROR: Physical file does not exist, C:\Users\datafile.zip.xls.

/* detect proper delim for UNIX vs. Windows */

%let delim=%sysfunc(ifc(%eval(&sysscp. = WIN),\,/));

/* create a name for our downloaded ZIP */

%let ziploc = %sysfunc(getoption(work))&delim.datafile.zip;

filename download "&ziploc";

/* Download the ZIP file from the Internet*/

proc http

method='GET'

url="http://www.philadelphiafed.org/research-and-data/real-time-center/business-conditions-index/ads_vintages.zip"

out=download;

run;

/* Import an excel file directly from the ZIP */

PROC IMPORT OUT= WORK.test

DATAFILE= "&ziploc"

DBMS=xls REPLACE;

GETNAMES=YES;

DATAROW=2;

RUN;

Re: Importing xls directly from Zip files

ballardw — Mon, 22 Sep 2014 23:10:53 GMT

Did you actually get a file downloaded?

Re: Importing xls directly from Zip files

Tom — Tue, 23 Sep 2014 01:09:45 GMT

You should have been able to make a filename pointing to the ZIP file using the ZIP engine (at least in SAS 9,4).

filename zipfile zip "&ziploc";

And then reference the XLS file within that by using ZIPFILE("ads_vintages.xls") .

But PROC IMPORT is not smart enough to recognize that perfectly normal SAS syntax.

4 proc import datafile=zipfile("&basename..xls") out=want

200

ERROR 22-322: Syntax error, expecting one of the following: ;, DATAFILE,

DATATABLE, DBMS, DEBUG, FILE, OUT, REPLACE, TABLE,

_DEBUG_.

ERROR 200-322: The symbol is not recognized and will be ignored.

So you will need to first copy the XLS file out of the ZIP file before you can point PROC IMPORT to it.

%let path=c:\downloads;

%let basename=ads_vintages;

%let ziploc=&path\&basename..zip ;

%let xlsfile=&path\&basename..xls ;

filename zipfile zip "&ziploc";

data _null_;

infile zipfile("&basename..xls") recfm=f lrecl=1;

file "&xlsfile" recfm=f lrecl=1;

input ch $char1.;

put ch $char1.;

run;

filename zipfile clear;

proc import datafile="&xlsfile" out=want

dbms=xls replace

;

getnames=yes;

run;

Re: Importing xls directly from Zip files

blue_lion — Tue, 23 Sep 2014 16:17:50 GMT

Hi Tom,

Thank you for clarifying everything. Copying the XLS file method works beautifully!

I did not know PROC IMPORT cannot recognize ZIPFILE("ads_vintages.xls").

That is how I tried to code initially because I was able to do so with txt files, but I also got error message and I removed the line.

Thank you very much!

Re: Importing xls directly from Zip files

venkatnaveen — Wed, 24 Sep 2014 14:12:30 GMT

Hi Tom I m getting folowing error when trying on sas 9.2 version.

Does zip engine support 9.2 version?

%let path=C:\Users\venkatnaveen\AppData\Local\Temp;

40 %let basename=ads_vintages;

42 %let ziploc=&path\&basename..zip ;

44 %let xlsfile=&path\&basename..xls ;

46 filename zipfile zip "&ziploc";

ERROR: Invalid device type.

ERROR: Error in the FILENAME statement.

48 data _null_;

50 infile zipfile("&basename..xls") recfm=f lrecl=1;

52 file "&xlsfile" recfm=f lrecl=1;

54 input ch $char1.;

56 put ch $char1.;

58 run;

ERROR: No logical assign for filename ZIPFILE.

ERROR: Physical file does not exist, C:\Users\venkatnaveen\AppData\Local\Temp\ads_vintages.xls.

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

60 filename zipfile clear;

WARNING: No logical assign for filename ZIPFILE.

62 proc import datafile="&xlsfile" out=want

64 dbms=xls replace

66 ;

68 getnames=yes;

70 run;

ERROR: Physical file does not exist, C:\Users\venkatnaveen\AppData\Local\Temp\ads_vintages.xls.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.96 seconds

cpu time 0.00 seconds

Re: Importing xls directly from Zip files

SASKiwi — Wed, 24 Sep 2014 19:31:35 GMT

I think this was a SAS 9.4 enhancement:

Reading and updating ZIP files with FILENAME ZIP - The SAS Dummy