topic Re: %sysfunc(exist, excel-tab-name) always returns 1 when using the XLSX engine on Excel spreadsheet in SAS Programming

%sysfunc(exist, excel-tab-name) always returns 1 when using the XLSX engine on Excel spreadsheet

DDutton — Wed, 15 May 2019 19:36:02 GMT

Here is the problem:

libname anylib XLSX "myspreadsheet.xlsx" access=readonly;

%put exist= %sysfunc(exist(anylib.Study)) ;
%put exist= %sysfunc(exist(anylib.Domains)) ;
%put exist= %sysfunc(exist(anylib.Domainsxxx)) ;
proc datasets lib=_ddtlib;
run;
quit;

libname _ddtlib clear;

Given the situation in which the spreadsheet does contain a tab named Study, the EXIST function should return a 1.

But if it does not contain a tab named Domains or Domainsxxx, it should return a 0.

Instead it is returning a 1. In fact, as far as I can tell, it always returns a 1, which is very misleading.

If the EXIST function is unable to tell if this tab exists in the spreadsheet, then I would expect a) that it would be documented in the doc on the XLSX engine (if it is, I can't find it), and b) that it return a 0 or missing value.

Here is my log ( edited to remove spreadsheet name), The spreadsheet does NOT contain the tab Study or Domainsxxx. The PROC DATASETS output correctly shows all of the tabs, listed with type=DATA.

62 libname _ddtlib XLSX "path to spreadsheet\name.xlsx" access=readonly;
NOTE: Libref _DDTLIB was successfully assigned as follows:
Engine: XLSX
Physical Name: \\path to spreadsheet\name.xlsx
63
64 %put exist= %sysfunc(exist(_ddtlib.Study)) ;
exist= 1
65 %put exist= %sysfunc(exist(_ddtlib.Domains)) ;
exist= 1
66 %put exist= %sysfunc(exist(_ddtlib.Domainsxxx)) ;
exist= 1
67 proc datasets lib=_ddtlib;
68 run;
69 quit;
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.07 seconds
cpu time 0.07 seconds
70
71 libname _ddtlib clear;
NOTE: Libref _DDTLIB has been deassigned.
72

Has anyone else seen this? As it is, I'm going to have to go to a different approach to testing the existence.

Re: %sysfunc(exist, excel-tab-name) always returns 1 when using the XLSX engine on Excel spreadsheet

Tom — Wed, 15 May 2019 21:05:59 GMT

I see the same thing. You should raise it with SAS Support.

Here is a little macro. Not sure if you can suppress the ERROR in the log.

%macro xlsx_exist(data);
%local ret dsid;
%let ret=1;
%let dsid = %sysfunc(open(&data));
%if &dsid %then %let dsid=%sysfunc(close(&dsid));
%else %let ret=0;
&ret.
%mend xlsx_exist;

130  %put in.class = %xlsx_exist(in.class);
in.class = 1
131  %put in.no_such_file = %xlsx_exist(in.no_such_file);
ERROR: Couldn't find range or sheet in spreadsheet
in.no_such_file = 0

So DICTIONARY.MEMBERS has the list of members in the XLSX file. Here is version that uses that information to test.

%macro xlsx_exist(libname,memname);
%local ret dsid;
%let ret=-1;
%let dsid = %sysfunc(open(sashelp.vmember(where=(libname=%upcase("&libname") and memname=%upcase("&memname")))));
%if &dsid %then %do;
  %let ret=%eval(0=%sysfunc(fetch(&dsid)));
  %let dsid=%sysfunc(close(&dsid));
%end;
&ret.
%mend xlsx_exist;

162  %put in.class = %xlsx_exist(in,class);
in.class = 1
163  %put in.no_such_file = %xlsx_exist(in,no_such_file);
in.no_such_file = 0

Re: %sysfunc(exist, excel-tab-name) always returns 1 when using the XLSX engine on Excel spreadsheet

ballardw — Wed, 15 May 2019 21:54:40 GMT

I would be somewhat hesitant to rely too much on the Dictionary tables for XLSX.

I just did a test for (obviously my files):

libname junk xlsx "x:\data\junk.xlsx";


proc sql;
   select *
   from dictionary.tables
   where libname='JUNK'
   ;
quit;

Which reported a single member, GRAPH 1 - THE SGPLOT PROCEDU, with a Member type of data. The sheet is a graph so I question the report of member type.

Re: %sysfunc(exist, excel-tab-name) always returns 1 when using the XLSX engine on Excel spreadsheet

Tom — Thu, 16 May 2019 01:43:49 GMT

Everything is going to look like DATA to the XLSX libname engine. When the only thing have is a hammer everything looks like a nail.

Re: %sysfunc(exist, excel-tab-name) always returns 1 when using the XLSX engine on Excel spreadsheet

DDutton — Thu, 16 May 2019 19:28:36 GMT

Here is some follow up. Yes, I do plan to pass it along to Tech Support, as I think that the documentation on the XLSX engine should include a note about it. The only thing relevant that I found is this in the EXIST documentation, "If you use a sequential library, then the results of the EXIST function are undefined.". However, a) we don't get a blank or missing back from the function - we get 1, and b) I suppose that the XLSX engine could be considered sort of sequential, in that you cannot update or append to a tab, but you can jump around from tab to tab, so it is not completely sequential either. And if that is the argument for the function not working correctly, then again, it should be documented.

As far as other techniques for verifying the existence of a tab on the spreadsheet, one more thing to note is that if the spreadsheet is open somewhere else in read/write mode, the libname assignment will be successful, but errors will result if you actually try to read from the spreadsheet (even with the access=readonly option specified). The OPEN function also fails. In addition, when this happens, the libname and memnames do not exist in dictionary.tables. So I have gone to a 2-step verification process.

/* spreadsheet level error checking */
%let lib_available = 0;
%let mytab_exist = 0;

proc sql noprint;
/* be sure spreadsheet can be read - it may be open */
select count(*) into :lib_available
from dictionary.tables
where libname eq "MYLIB"
;
/* see if MYTAB tab exists */
select count(*) into :mytab_exist
from dictionary.tables
where libname eq "MYLIB" and memname eq "MYTAB "
;
quit;

%if &lib_available eq 0 %then
%do; /* spreadsheet is not available - probably is open */
%put ERROR: spreadsheetname..xlsx cannot be read. It may be open. Macro will stop.;
%goto _stop0_;
%end;

%else %if &mytab_exist eq 1 %then
%do; /* whatever I want to do */

It's longer, but it works.

Thanks for all the input.

Re: %sysfunc(exist, excel-tab-name) always returns 1 when using the XLSX engine on Excel spreadsheet

DDutton — Thu, 16 May 2019 19:30:24 GMT

I did not see this behavior in SAS 9.4M3