How to load excel with multiple sheets dynamically into SAS datset?

[StickyRoll]

I have an excel workbook which contains random sheets every month.

As such, I want to have a script to load this excel workbook without hardcode any of the worksheet name inside the script. Is there a way in SAS where I can get the list of all sheets in the excel?

This way, I can assign the list to macro and easily store in SAS. 

[Patrick]

A libname using the xlsx engine will allow you to access the sheets directly like SAS tables under a single libref. Example here in one of Chris Hemedinger's fantastic blogs. 

You then can just use a proc datasets/copy to copy all these Excel sheets to another libref which uses the SAS engine - and they will "magically" become SAS tables.

Not sure how you intend to deal with such "random" tables - is it only the names or will also the table structures differ - but you don't provide the information to make any further statements.

[StickyRoll]

@Patrick 

I dont see how this tutorial show us how SAS dynamically read all excel sheets/tabs automatically. I understand that I can use libname XLSX engine to read excel. 

I have an excel workbook that contains 5 sheets/tabs. I don't want to hardcode the sheets/tabs in any part of the SAS script. What I want is to automatically load in all excel sheets as tables.

If I have 5 sheets, I want to create 5 datasets, all without hardcode the actual name of the excel sheets.

[StickyRoll]

here is my code:

libname xl XLSX "D:\Excel.xlsx;


proc contents data=xl._ALL_;

RUN;

I get a warning

"no matching members in directory".

However, the sample in the tutorial shows 3 members which I believe is the sheets.

Why is this happening? My excel has 3 sheets as well.

[PaigeMiller · Accepted solution]

Here is my modification that works for me, and also allows sheet names and variable names to have special characters or blanks.

libname xl XLSX "D:\Excel.xlsx";

options validvarname=v7;

proc copy in=xl out=work memtype=data;
run;
quit;

By the way, may I make a request @StickyRoll ? From now on please paste the actual code (or better yet, paste the log so we can see the code and the ERRORs, WARNINGs and NOTEs) into your message. The code you showed cannot possibly work, because your LIBNAME statement is wrong, and is missing a double quote after the .xlsx, and that could be the cause of the error you are seeing.

[StickyRoll]

@PaigeMiller Hi there,

I have this warning:

28         libname xl XLSX "D:\SASexcel.xlsx"; 
NOTE: Libref XL was successfully assigned as follows: 
      Engine:        XLSX 
      Physical Name: D:\SASexcel.xlsx
29         
30         options validvarname=V7;


31         proc copy in=xl out=work memtype=data;
32         run;

WARNING: Input library XL is empty.

My script as below.

libname xl XLSX "D:\SASexcel.xlsx"; 

options validvarname=V7;
proc copy in=xl out=work memtype=data;
run;
quit;

I think SAS couldn't read my excel sheets/tabs. Not sure what caused that. Did you see anything wrong with my script?

[PaigeMiller]

Does the file D:\SASexcel.xlsx actually exist? The message you are getting is the same message I get when I use a LIBNAME that points to an Excel file that doesn't exist.

[Tom]

Most likely the path you have for the filename does not exists.  SAS does not know that you intend to READ from that new XL libref. You might be intending to create a new XLSX file so that you can WRITE to it.

Make sure the file exists.

data _null_;
  filename = "D:\SASexcel.xlsx"; 
  if fileexist(filename) then put filename=:$quote. 'exists.';
  else put filename=:$quote. 'does NOT exist.';
run;

[StickyRoll]

@Tom Spot on.

I tried to delete the excel and create a dummy excel. Using the script provided by @PaigeMiller worked like a charm with the new excel.

Thanks @Tom @PaigeMiller @Patrick for the help. Appreciate it.