topic Reading XLSX inside a ZIP file in SAS Programming

Reading XLSX inside a ZIP file

asabou01 — Tue, 07 Apr 2020 14:20:18 GMT

I am trying to read a XLSX file that is "compressed" in Windows 10 I assume WinZip. When I point to it I get: Insufficient authorization to access error

filename in1 zip 'C:\myzipfile.zip' ;

data _null_;
infile in1;
input;
put _infile_;
run;

Re: Reading XLSX inside a ZIP file

Kurt_Bremser — Tue, 07 Apr 2020 14:24:37 GMT

Do you have a standalone SAS installation, or do you work in a client/server environment? If the latter, you can't access your desktop drive like this.

Re: Reading XLSX inside a ZIP file

Tom — Tue, 07 Apr 2020 14:25:33 GMT

A ZIP file is like a directory. What file inside the ZIP file are you trying to read? You need to specify the member name. Either in the FILENAME statement or the INFILE statement.

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

If it is an XLSX file then you will need to first copy it out to an actual file for the XLSX libname engine to work on it.

Re: Reading XLSX inside a ZIP file

asabou01 — Tue, 07 Apr 2020 14:35:44 GMT

No it is a network drive (client/server)

Re: Reading XLSX inside a ZIP file

Kurt_Bremser — Tue, 07 Apr 2020 14:51:16 GMT

C: is NOT a network share. It is always the local disk from which Windows was started.

I was NOT asking about your disk drives, I was asking about your SAS installation. Be precise in your wording.

Re: Reading XLSX inside a ZIP file

asabou01 — Tue, 07 Apr 2020 15:01:56 GMT

Sorry about "misspeaking" on my part. Windows 10 SAS installed on local PC. I added a "member" and I was able to read it.

The member is in XLS format and not newer EXCEL version (XLSX). Do I need now to define all columns in XLS file in a datastep? I have been using "Proc Import" after un-compressing the file in the past. Thanks very much!

filename in1 ZIP 'C:\health.zip' member="myhealth.xls";

data be;
infile in1;
input;
run;

Re: Reading XLSX inside a ZIP file

Tom — Tue, 07 Apr 2020 15:27:46 GMT

You cannot read an XLS file like it was text. You could try to read it as binary data, but it wouldn't make sense to do that. Instead use PROC IMPORT to convert on of the sheets in the XLS file into a SAS dataset.

You could try using your fileref of IN1 with PROC IMPORT,

filename in1 ZIP 'C:\health.zip' member="myhealth.xls" recfm=f lrecl=512;
proc import datafile=in1 dbms=xls out=myhealth replace;
run;

but I suspect that you will have to copy the file out of the ZIP into a real physical file and point PROC IMPORT at that physical file instead. You could use a temporary file.

filename in1 ZIP 'C:\health.zip' member="myhealth.xls" recfm=f lrecl=512;
filename copy temp recfm=f lrecl=512;
data _null_;
  rc=fcopy('in1','copy');
  put rc=;
run;

proc import datafile=copy dbms=xls out=myhealth replace;
run;
proc contents data=myhealth;
run;

Re: Reading XLSX inside a ZIP file

asabou01 — Tue, 07 Apr 2020 15:40:50 GMT

Thank you so much!