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)?
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
The solution provided only works when the relevant "engine" (in this case XLSX) is installed on the SAS server.
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!
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.
Ready to level-up your skills? Choose your own adventure.