check if excel files in a directory are empty or n

[Moksha]

Hi All,

       In a directory, I have xlsx files where some are having data and some are having only headings in the first row. I have to read all these excel files and check if it has only first row then write a message that it is empty and if the file has more than row then it is not an empty file. If we use fget to check whether it has data or not, then since these files have first row, hence it will not be considered as non-empty file. Hence, I am not sure how to check for empty excel files in this case.  Need a macro which takes the directory as parameter. Please, advise.

Thanks

[ballardw]

I would start with a LIBNAME to connect to the file and see how many observations a data set might have.

BUT this may not work because depending on how your Excel files were made you may have phantom data. No actual displayed values but the Excel sheets think that cells have something in them and so a data set may have multiple observations with all missing values.

[Moksha]

Thank you for the inputs. I have used LIBNAME statement to point to the directory containing the excel files.

[Kurt_Bremser]

LIBNAME XLSX is not to be used on directories, but individual Excel files.

Use the method suggested by @donricardo to locate the Excel files, define a libname for each given file, then use DICTIONARY.TABLES to see if the "datasets" (sheets) contain observations.

[donricardo]

To have SAS do the checking, one approach might be to 1) locate your Excel files; and 2) import them into SAS via, say a proc import; and 3) run a proc contents or vtables view to examine whether or not they actually contain data.    You can take advantage of some external file functions to help.  so, step 1, build a little macro definition to locate the Excel files.  So here is the general idea with some psudo coding:

%macro findmyxlsfiles(dir) /minoperator;

%local fileref rc did n memname didc;

%let rc = %sysfunc(filename(fileref,&dir));

%let did = %sysfunc(dopen(&fileref));

/*then build a loop to check all the files in the above directory*/

%do n=1 %to %sysfunc(dnum(&did));

   %let memname = %sysfunc(dread(&did,&n)); /*will give name of the dir/file num*/

  %if  %upcase(%scan(&memname,-1,.)) in XLS XLSX

      %then /*call or write some proc import code to import the above-found Excel file(s)

Now that the XLSX XLS files have been imported, you can use whatever you know in SAS to check the observation count.

[Moksha]

Thank you for the reply and the example code. I have used import to import the excel files to datasets. Now, after checking the count in each of these datasets, if there are no observations then I have to delete the dataset, since the requirement is that I should not import the excel files if there are no observations (first row is only for the column headings). Hence, I want to know before importing the excel files, if there is any way to check and if they are completely empty (i.e., they should not even have the column headings in the first row) , then only import. Now, as I understand, I think that there is some limitation in SAS and hence the only way is that first we have import all the excel files and then check if they are empty or not. 

[whymath]

If your excel file has extension ".xlsx", I think you can use this method. Well, this program is for single file, you may want to modify it for multiple files condition:

%let excelfile=C:\Profiles\Work\New Excel Worksheet.xlsx;
/*%let excelfile=C:\Profiles\Work\New Excel Worksheet_0row_edited.xlsx;*/
/*%let excelfile=C:\Profiles\Work\New Excel Worksheet_1row.xlsx;*/
filename excel zip "&excelfile";

data tab1;
  length memname $256;
  fid=dopen("excel");
  if fid=0 then stop;
  memcount=dnum(fid);
  do i=1 to memcount;
    memname=dread(fid,i);
    output;
  end;
  fid=dclose(fid);
run;

data tab2;
  set tab1(keep=memname) end=eof;
  where memname=:"xl/worksheets/" and scan(memname,-1,'.')='xml';

  file=scan(memname,-1,'/');
  rc=dosubl('
    option nonotes;
    data tab3;
      infile excel('||trim(memname)||') lrecl=32767 recfm=f truncover;
      input text $32767.;
      if prxmatch("/<v>.*?<\/v>/",trim(text)) then do;
        output;
        stop;
      end;
    run;
    %let m'||cats(_n_)||'=&sysnobs;
    option notes;
  ');
  rownum=symget(cats('m',_n_));
  if rownum>'0' then put "The Excel file &excelfile is not empty.";
  else put "The Excel file &excelfile is empty.";
run;

The unzip xlsx file skill, I learned from this post: Using FILENAME ZIP to unzip and read data files in SAS - The SAS Dummy

[Moksha]

Thank you very much. I will work with this for multiple files. Once, again thank you very much.

[Ksharp]

%let path=C:\temp\xlsx  ;  /*the path name for XLSX files*/




%macro check_xlsx(xlsx=);
proc import datafile="&xlsx." out=x dbms=xlsx replace;run;
%let dsid=%sysfunc(open(x));
%let any=%sysfunc(attrn(&dsid.,any));
%let dsid=%sysfunc(close(&dsid.));
%if &any.=0 or &any.=-1 %then %put ERROR: &xlsx. is an empty xlsx file. ;
%mend;

filename m temp;
data _null_;
file m;
length want $ 1000;
rc=filename('x',"&path.");
did=dopen('x');
do i=1 to dnum(did);
  fname=dread(did,i);
  if lowcase(scan(fname,-1,'.'))='xlsx' then do;
    want=cats('%check_xlsx(xlsx=',"&path.\",fname,')');
 put want;
  end;
end;
run;
%include m;

NOTE: Assuming Every xlsx file have only one SHEET.

[Moksha]

Thank you very for the sample code. I will check this.