Help using Base SAS procedures

Importing xls directly from Zip files

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Importing xls directly from Zip files

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;


Accepted Solutions
Solution
‎09-22-2014 09:09 PM
Super User
Super User
Posts: 7,039

Re: Importing xls directly from Zip files

Posted in reply to blue_lion

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;

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Importing xls directly from Zip files

Posted in reply to blue_lion

Did you actually get a file downloaded?

Solution
‎09-22-2014 09:09 PM
Super User
Super User
Posts: 7,039

Re: Importing xls directly from Zip files

Posted in reply to blue_lion

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;

Occasional Contributor
Posts: 14

Re: Importing xls directly from Zip files

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!

Frequent Contributor
Posts: 89

Re: Importing xls directly from Zip files

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

Super User
Posts: 3,250

Re: Importing xls directly from Zip files

Posted in reply to venkatnaveen
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1707 views
  • 1 like
  • 5 in conversation