The XLSX engine certainly seems to have some issues with the meta functions (such as EXIST) that the EXCEL engine does not.
Can you switch to EXCEL engine ?
If so, the sheet name is referenced as a data set using sheet name with $ suffix. When the sheet name is a 'non-name' construct (spaces, punctuation, etc) the referencing must be as a name literal. I.e. excel-libname."sheet name$"n
If you must use XLSX engine, you could write your own existence checking macro. Example:
%macro can_open(data);
%local dsid rc;
%let dsid = %sysfunc(open(&data));
%if &dsid %then %let rc = %sysfunc(close(&dsid));
%eval(&dsid > 0)
%mend;
libname sample XLSX 'sample.xlsx';
%put can_open=%can_open(sample.first) sample.first;
%put can_open=%can_open(sample.first$) sample.first$;
%put can_open=%can_open(sample.'2nd place'n) sample.'2nd place'n;
%put can_open=%can_open(sample.Does_not_exist) sample.Does_not_exist;
Sample code demonstrating weakness of XLSX engine, with regard to EXIST(), compared to strength of EXCEL engine.
ods _all_ close;
ods excel file='sample.xlsx';
ods excel options(sheet_name = 'First');
proc print data=sashelp.class;
run;
ods excel options(sheet_name = '2nd place');
proc print data=sashelp.class;
run;
ods excel options(sheet_name = '3rd_floor');
proc print data=sashelp.class;
run;
ods noresults; * turn off 'auto-open';
ods excel close;
ods results;
options nonotes nosource;
libname sample xlsx 'sample.xlsx';
data _null_;
array names[16] $20 (
'first', '"first"n', 'first$', '"first$"n',
'2nd place', '"2nd place"n', '2nd place$', '"2nd place$"n',
'3rd_floor', '"3rd_floor"n', '3rd_floor$', '"3rd_floor$"n',
'4th sheet', '"4th sheet"n', '4th sheet$', '"4th place$"n'
);
put / 30*'-' / 'XLSX engine - WEAK EXIST() !' / 30*'-' /;
do index=1 to dim(names);
name = names[index];
exist = exist('sample.' || name);
put name @20 exist=;
if mod(index,4)=0 then put;
end;
run;
libname sample EXCEL 'sample.xlsx';
data _null_;
array names[16] $20 (
'first', '"first"n', 'first$', '"first$"n',
'2nd place', '"2nd place"n', '2nd place$', '"2nd place$"n',
'3rd_floor', '"3rd_floor"n', '3rd_floor$', '"3rd_floor$"n',
'4th sheet', '"4th sheet"n', '4th sheet$', '"4th place$"n'
);
put / 30*'-' / 'EXCEL engine - PROPER EXIST() !' / 30*'-' /;
do index=1 to dim(names);
name = names[index];
exist = exist('sample.' || name);
put name @20 exist=;
if mod(index,4)=0 then put;
end;
run;
libname sample;
options notes source;
Log
------------------------------
XLSX engine - WEAK EXIST() !
------------------------------
first exist=1
"first"n exist=1
first$ exist=1
"first$"n exist=1
2nd place exist=0
"2nd place"n exist=1
2nd place$ exist=0
"2nd place$"n exist=1
3rd_floor exist=1
"3rd_floor"n exist=1
3rd_floor$ exist=1
"3rd_floor$"n exist=1
4th sheet exist=0
"4th sheet"n exist=1
4th sheet$ exist=0
"4th place$"n exist=1
------------------------------
EXCEL engine - PROPER EXIST() !
------------------------------
first exist=0
"first"n exist=0
first$ exist=1
"first$"n exist=1
2nd place exist=0
"2nd place"n exist=0
2nd place$ exist=0
"2nd place$"n exist=1
3rd_floor exist=0
"3rd_floor"n exist=0
3rd_floor$ exist=1
"3rd_floor$"n exist=1
4th sheet exist=0
"4th sheet"n exist=0
4th sheet$ exist=0
"4th place$"n exist=0
... View more
