- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Your log is incomplete, as we can't see the log of the data step.
What are your settings for options validvarname and validmemname?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Yes indeed my file was opened in another application! Thanks a lot!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.