BookmarkSubscribeRSS Feed
hamed_majidi_gmail_com
Calcite | Level 5

Hi,

 

I want to read an Excel file with multiple worksheets into SAS. The worksheets are named. Is there any way I can refer to a specific worksheet, for example Sheet2, without knowing its name (i.e., using a parameter rather than its name)?

2 REPLIES 2
tsap
Pyrite | Level 9

I'm not exactly sure what parameters you would like to use to pull in specific sheets from an excel document, so I can't speak directly to that point. However, there is a way to pull in data from an excel document with multiple sheets without specifically knowing the names of the sheets.

 

OPTIONS VALIDVARNAME=ANY;
 
LIBNAME EXCL_IMP  XLSX  "Filepath/Sample_Data.xlsx";
 
PROC SQL;
CREATE TABLE WORK.SheetNames AS
	SELECT
		  MemName		AS Sheet
	FROM Dictionary.Columns
	WHERE Libname = 'EXCL_IMP';
QUIT;

The 'ValidVarName' option is set in case any of the excel sheet names have spaces between words.

 

In the Libname assignment statement, just fill in the filepath and excel file name within the quotes. You can name the Libref whatever you prefer as well.

 

Then there are any number of ways to get the sheet names after the libname assignment. I specifically chose a PROC SQL query against the Dictionary.Columns table. Ensure that when you put your Libref value in the WHERE statement, that you capitalize the entire value. The libname values on this table are all capitalized and if you do not put the capitalized version of your libref in the WHERE statement, you will get 0 results.

 

This will show you a table with all of the sheet names for this excel document.

 

If you wanted to go one step further with this logic, you could always replace this PROC SQL step that generates a table with the sheet names, with logic that creates a macro variable that populates a text string with each of the sheet names, and use that with a macro statement to import each of the sheets accordingly.

 

The logic to load the sheet names to a macro variable as a list would look something like this:

PROC SQL;
	SELECT DISTINCT MemName	INTO :SheetNames separated by ' '
	FROM Dictionary.Columns
	WHERE Libname = 'EXCL_IMP';
QUIT;

%PUT &=SheetNames;

Hope this helps

 

ishmo65
Obsidian | Level 7

The solution provided only works when the relevant "engine" (in this case XLSX) is installed on the SAS server.

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
  • 2 replies
  • 1784 views
  • 0 likes
  • 3 in conversation