Help using Base SAS procedures

Read a zipped Excel file ??

Reply
Regular Contributor
Posts: 173

Read a zipped Excel file ??

Hello Everyone,

I am trying to read an Excel file that has been zipped.  The Excel file name is new_file.xlsx.  It has been zipped and sits in c:\docs\new_file.zip.

How do I read this file without manually unzipping it first?  I have tried Proc Import and x command.  Here is my attempt using x command:

"cd C:\Program Files\7-Zip\";

'7z.exe e -o C:\docs\OUTPUT.xlsx C:\docs\new_file.zip';

When I run this code I don't get any errors.  It does not seem to produce any output either.

Any suggestions would be greatly appreciated.

Thank you! 

Super Contributor
Posts: 345

Re: Read a zipped Excel file ??

With SAS 9.4 you can use the filename statement to access a zip file. Have a look at FILENAME Statement, ZIP Access Method :: SAS(R) 9.4 Statements: Reference, Third Edition.

Super User
Posts: 7,771

Re: Read a zipped Excel file ??

To get the output of external commands into the log, use this construct:

filename oscmd pipe "your_command 2>&1";* the 2>&1 redirects stderr output to stdout;

data _null_;

infile oscmd;

input;

put _infile_;

run;

You won't be able to do it all in one step, though; the ZIP file access method can only read text from zipped files. So you will have to unzip the file first and then use one of the excel access methods (probably libname xlsx) to read from the .xlsx.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 3,212

Re: Read a zipped Excel file ??

When it is only an excel file xlsx type being zipped it makes no sense.  The xlsx file is an zip-file having xml files and references inside it. You can open an xlsx file with 7zip yourself yourself.

---->-- ja karman --<-----
Community Manager
Posts: 2,953

Re: Read a zipped Excel file ??

As Andreas pointed out, FILENAME ZIP can be used to extract the file, and then you can read the result with PROC IMPORT.  See examples of using FILENAME ZIP in this blog post.

Chris

Ask a Question
Discussion stats
  • 4 replies
  • 663 views
  • 0 likes
  • 5 in conversation