BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
StickyRoll
Fluorite | Level 6

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. 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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.

 

--
Paige Miller

View solution in original post

8 REPLIES 8
Patrick
Opal | Level 21

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
Fluorite | Level 6

@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
Fluorite | Level 6

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
Diamond | Level 26

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.

 

--
Paige Miller
StickyRoll
Fluorite | Level 6

@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
Diamond | Level 26

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.

 

 

 

--
Paige Miller
Tom
Super User Tom
Super User

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
Fluorite | Level 6

@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.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 2342 views
  • 0 likes
  • 4 in conversation