CHECK FOR EMPTY XL SHEET

Reply
Occasional Contributor
Posts: 8

CHECK FOR EMPTY XL SHEET

Hi, Is there any way to check whether an excel sheet is empty.(out of 5 sheets in a excel workbook)

Grand Advisor
Posts: 9,584

Re: CHECK FOR EMPTY XL SHEET

One way is using libname ..  excel  , then check if there is a table has only one obs and all of value are missing .

Super Contributor
Posts: 336

Re: CHECK FOR EMPTY XL SHEET

Ksharp already posted the libname-idea. A simplistic implementation is:

Libname XL Excel Path="C:\Users\Excelfile.xlsx"; /* path & excel-file-name required */
Libname XL_dummy "C:\Users "; /* only Path */

Proc Datasets Memtype=Data Nolist;
  Copy In=XL Out=XL_dummy;
Run;

Proc Datasets Library=XL_dummy MemType=Data NoList;
  Contents Out=Work.XL_Contents (Keep=MemName Nobs) Data=_ALL_ NoPrint;
Run;

Proc Sort Data=XL_Contents Nodupkey;
  By MemName;
Run;

Data XL_Contents;
  Set XL_Contents;
  MemName=Translate(MemName,'','$');
  If Nobs ge 1 Then Info="Contains data";
  Else Info="Is empty";
Run;

Ask a Question
Discussion stats
  • 2 replies
  • 253 views
  • 6 likes
  • 3 in conversation