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
