topic Re: Reading XLS Inside ZIP in SAS Programming

Reading XLS Inside ZIP

Junyong — Thu, 14 Nov 2019 21:48:03 GMT

If there is a CSV file inside a ZIP file, for example, then we can directly import the data using a DATA step as follows.

filename have zip "!userprofile\desktop\have.zip";
data dataset;
infile have(filetoread.csv) dsd;
input year one two three;
run;

The DATA step does not read an XLS file, so we need PROC IMPORT instead. Does PROC IMPORT provide something similar? Now I am using DATA _NULL_ with INFILE, FILE, INPUT, PUT, etc. to unzip first before reading but wonder whether there is a direct way to avoid unzipping and directly access inside.

Re: Reading XLS Inside ZIP

ChrisNZ — Fri, 15 Nov 2019 09:41:27 GMT

Have you seen the member= option?

https://documentation.sas.com/?docsetId=lestmtsglobal&docsetTarget=n1dn0f61yfyzton1l2ngsa1clllr.htm&docsetVersion=9.4

Re: Reading XLS Inside ZIP

Tom — Fri, 15 Nov 2019 15:02:01 GMT

@Junyong wrote:

If there is a CSV file inside a ZIP file, for example, then we can directly import the data using a DATA step as follows.
filename have zip "!userprofile\desktop\have.zip";
data dataset;
infile have(filetoread.csv) dsd;
input year one two three;
run;
The DATA step does not read an XLS file, so we need PROC IMPORT instead. Does PROC IMPORT provide something similar? Now I am using DATA _NULL_ with INFILE, FILE, INPUT, PUT, etc. to unzip first before reading but wonder whether there is a direct way to avoid unzipping and directly access inside.

Does the ZIP contain an XLS file or a CSV file? You cannot read an XLS file with a data step. It is a binary format. You could read the CSV file. Normally you would put quotes around the member name in your reference to the member of the aggregate storage location that the HAVE libref is pointing to.

infile have("filetoread.csv") dsd;

Also make sure that the file is not saved with a path in the ZIP file. If it is then you will need to include that path as part of the quoted name.

Re: Reading XLS Inside ZIP

Junyong — Fri, 15 Nov 2019 15:14:27 GMT

Thanks for this comment, but it seems this code doesn't work.

filename down1 "%sysfunc(getoption(work))\download.zip";
proc http method="get" out=down1
url="http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_CSV.zip";
run;
filename down2 zip "%sysfunc(getoption(work))\download.zip" member="F-F_Research_Data_Factors.CSV";
proc import file=down2 dbms=csv out=data1 replace;
run;

PROC HTTP downloads down1 correctly, but PROC IMPORT fails to read down2 inside.

Re: Reading XLS Inside ZIP

Tom — Fri, 15 Nov 2019 15:17:36 GMT

I think that PROC IMPORT has some quirk that makes it not work with members from a ZIP file.

Try copying the file first.

filename down1 "%sysfunc(getoption(work))\download.zip";
proc http method="get" out=down1
url="http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/F-F_Research_Data_Factors_CSV.zip";
run;
filename down2 zip "%sysfunc(getoption(work))\download.zip" member="F-F_Research_Data_Factors.CSV";
filename down3 temp;
data _null_;
  infile down2;
  file down3;
  input;
  put _infile_;
run;
proc import file=down3 dbms=csv out=data1 replace;
run;

Re: Reading XLS Inside ZIP

Junyong — Fri, 15 Nov 2019 15:17:43 GMT

Thanks for this reply. I know DATA doesn't accept XLS, but wonder if PROC IMPORT can directly access to an XLS member file inside a ZIP file as DATA can.

Re: Reading XLS Inside ZIP

Tom — Fri, 15 Nov 2019 15:39:56 GMT

@Junyong wrote:

Thanks for this reply. I know DATA doesn't accept XLS, but wonder if PROC IMPORT can directly access to an XLS member file inside a ZIP file as DATA can.

Same answer:

I think that PROC IMPORT has some quirk that makes it not work with members from a ZIP file.

Although for a binary file like an XLS file you will need to use FILECOPY or some other method instead of the simple line by line copy that will work with a text file.