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

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1035 views
  • 0 likes
  • 3 in conversation