Exist function and xlsx file

centilen — Tue, 17 Mar 2020 11:13:58 GMT

Hi there,

This is an example code. I have a xlsx file with multiple sheets. One sheet name its given to me (dsname) and i would like to control if its correct or not. If it is, do a set. I use a macro because its a recursive action.

%let dsname=AE; /*Given to me*/

%macro doASet(name);

libname test xlsx "C:\nameOfFile"; 

%if %sysfunc(exist(test.&name.)) %then %do;

	data out;
		set test."&name"n; /*for example*/
		a = 8;
	run;
%end;
%else %put ---Data set &name does not exist.;
%mend doASet;

%opends(dsname);

If the sheet exist, i do a data set and its all correct but if the sheet doesn't i wish to display and error like that or do another thing, but actually exist funct return 1. Always return 1!

I tried tones of combinations and I don't find the solution.

I thank you in advance for your help. Regards

Re: Exist function and xlsx file

PaigeMiller — Tue, 17 Mar 2020 11:47:37 GMT

I couldn't get the EXIST() function to work either.

However, PROC CONTENTS will find the names of the tabs in the Excel file and put them in a data set which you can search.

ods output members=members;
proc contents data=test._all_ out=_contents_ nods;
run;

Re: Exist function and xlsx file

RichardDeVen — Tue, 17 Mar 2020 12:58:15 GMT

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

topic Re: Exist function and xlsx file in SAS Enterprise Guide

Exist function and xlsx file

Re: Exist function and xlsx file

Re: Exist function and xlsx file