Importing xls directly from Zip files

[blue_lion]

Hi

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

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;

[ballardw]

Did you actually get a file downloaded?

[Tom · Accepted solution]

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

                                 -

                                 22

                                 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;

[blue_lion]

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!

[venkatnaveen]

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;

39

40   %let basename=ads_vintages;

41

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

43

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

45

46   filename zipfile zip "&ziploc";

ERROR: Invalid device type.

ERROR: Error in the FILENAME statement.

47

48   data _null_;

49

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

51

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

53

54     input ch $char1.;

55

56     put ch $char1.;

57

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

59

60   filename zipfile clear;

WARNING: No logical assign for filename ZIPFILE.

61

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

63

64     dbms=xls replace

65

66   ;

67

68     getnames=yes;

69

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

[SASKiwi]

I think this was a SAS 9.4 enhancement:

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