I have the syntax as follows:
libname xl xlsx "insert path here.xlsx";
proc sql;
drop table xl.tablename;
quit;
data xl.tablename;
set differenttablename;
run;
I am getting the error:
ERROR: XL.tablename.DATA cannot be deleted because files cannot be deleted from the XL library.
I did not have an issue with this prior to a recent upgrade to this version of SAS. I am working in SAS EG 7.1. Has the syntax changed? What is this error?
Might want to check file settings for write, or the folder permissions.
If you or someone else downloaded the xlsx file it may have been set as read only during the download process or some other protection feature implemented. Go to the file menu in Excel and examine the properties and adjust if needed.
Works in 9.4M2 64bit Win 7 64bit
%utlfkil(d:/xls/class.xlsx);
libname xl "d:/xls/class.xlsx";
data xl.tablename;
set sashelp.class;
run;
proc sql;
drop table xl.tablename;
quit;
data xl.tablename;
set sashelp.class;
run;
libname xl clear;
393 %utlfkil(d:/xls/class.xlsx); * delete if exists;
394 libname xl "d:/xls/class.xlsx";
NOTE: Libref XL was successfully assigned as follows:
Engine: EXCEL
Physical Name: d:/xls/class.xlsx
395 data xl.tablename;
396 set sashelp.class;
397 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set XL.tablename has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 412.56k
OS Memory 15848.00k
Timestamp 01/04/2017 01:21:29 PM
Step Count 106 Switch Count 0
398 proc sql;
399 drop table xl.tablename;
NOTE: Table XL.tablename has been dropped.
400 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 69.25k
OS Memory 15848.00k
Timestamp 01/04/2017 01:21:29 PM
Step Count 107 Switch Count 0
401 data xl.tablename;
402 set sashelp.class;
403 run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set XL.tablename has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.01 seconds
memory 409.12k
OS Memory 15848.00k
Timestamp 01/04/2017 01:21:29 PM
Step Count 108 Switch Count 0
404 libname xl clear;
NOTE: Libref XL has been deassigned.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.