topic Re: Reading Microsoft Excel Data with SAS/ACCESS LIBNAME statement in SAS Programming

Reading Microsoft Excel Data with SAS/ACCESS LIBNAME statement

Zatere — Tue, 19 Feb 2019 11:31:52 GMT

Hi there,

I am trying to read excel files in SAS using SAS/ACCESS LIBNAME statement. I have checked the following:

proc setinit; run; --> I have SAS/ACCESS Interface to PC Files
I am running SAS 9.4 with Microsoft Excel 2016

I use the following code:

libname results xlsx "C:\Users\My Files\myfile.xlsx";

data work.test;   
set results.'Sheet1'n;
run;

I am getting the following on the SAS Log:

331  libname results xlsx "C:\Users\Documents\My Files\myfile.xlsx";
NOTE: Libref RESULTS was successfully assigned as follows:
      Engine:        XLSX
      Physical Name: C:\Users\My Files\myfile.xlsx

And below is the remaining SAS log:

347  proc contents data=results._all_;

WARNING: No matching members in directory.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds


348  data work.test;
349  set results.'Sheet1'n;
ERROR: Error opening XLSX file -> C:\Users\Documents\My SAS Files\myfile.xlsx .  It is
either not an Excel spreadsheet or it is damaged.   Error code=80001019
Requested Input File Is Invalid
ERROR: File RESULTS.Sheet1.DATA does not exist.
350  run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete.  When this step was stopped there were 0
         observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds

However, the library results is empty. I have looked at the community for a similar issue but could not find anything.

Could you please help on that?

Thanks and regards!

Re: Reading Microsoft Excel Data with SAS/ACCESS LIBNAME statement

Kurt_Bremser — Tue, 19 Feb 2019 11:25:21 GMT

Your log is incomplete, as we can't see the log of the data step.

What are your settings for options validvarname and validmemname?

Re: Reading Microsoft Excel Data with SAS/ACCESS LIBNAME statement

Zatere — Tue, 19 Feb 2019 11:32:55 GMT

Hi, I edited my original post. I am not quite sure about "What are your settings for options validvarname and validmemname?". Thanks

Re: Reading Microsoft Excel Data with SAS/ACCESS LIBNAME statement

Kurt_Bremser — Tue, 19 Feb 2019 11:43:08 GMT

This is your clue:

ERROR: Error opening XLSX file -> C:\Users\Documents\My SAS Files\myfile.xlsx .  It is
either not an Excel spreadsheet or it is damaged.   Error code=80001019
Requested Input File Is Invalid

The file is not recognized by SAS as a file according to the XLSX format. It might be that someone cheated on you and gave you a classic XLS file with a wrong extension, or something different altogether which is opened by Excel as a spreadsheet (think csv).

Or the file is simply still opened (or thinks it is) by Excel, see https://blogs.sas.com/content/sasdummy/2014/09/24/proc-import-xlsx-error/

This link is the first that turns up on a google search for "Error code=80001019", see Maxim 6.

Re: Reading Microsoft Excel Data with SAS/ACCESS LIBNAME statement

Kurt_Bremser — Tue, 19 Feb 2019 11:46:00 GMT

And please don't edit previous posts with new information, as that does not trigger an email notification. Adding a new message to a thread does so automatically.

Re: Reading Microsoft Excel Data with SAS/ACCESS LIBNAME statement

Zatere — Tue, 19 Feb 2019 11:45:39 GMT

Yes indeed my file was opened in another application! Thanks a lot!