If your using SAS 9.4 and the file really is XLSX you can do:
libname tmp excel "<path>\<yourfile>.xlsx";
This will scan the XLSX file and set a dataset for every sheet/range it finds. Then you can simply check sashelp.vtable for the "TMP" libname to see what ranges/sheets are available.
However, I would really advise not using Excel for getting data. The whole process you describe is so flawed in numerous ways:
Excel - this is not in any sense a structured or controlled data format (i.e. you could put anything in any cell). This will cause you so many headaches.
Proc import - this is a guessing procedure, its tries to guess what the data is and sometimes gets it wrong.
Macro - this is used for generating text, nothing more. You will just compound the above issues by then wrapping macro around it all.
So, as with any programming, first sit down and design the process (no coding), what needs to go where, what file formats and why, how to handle each, see what links etc. Once you have a plan choose the best approach - say for instance this Excel file is sent by a vendor - return to the vendor and ask them to supply in a useable format stating that using Excel will require more resource and be unstable etc. Also get an import agreement on the data so that if anything changes, or the data is not as you expect, then you have an agreed document to wave in their face rather than the task of re-programming yourself. A data import agreement would also eliminate the problem you face as it would define exactly what sheets are required.
Dear RW9, thanks for your kind reply, i tried use LIBNAME way but not work well in hosted SAS 9.3:
ERROR: The EXCEL engine cannot be found.
ERROR: Error in the LIBNAME statement.
Do you know other ways to deal with this judgement condition? data source is settled as XLSX, may i use some other ways in macro to add a condition per this point? thanks very much.
You misunderstand the use of Macro. It in itself does nothing, it only creates string data output - a bit like Find/Replace.
If this "data source is settled as XLSX" is true, then you are going to have no end of problems with it. Not going over it, you will find out over time.
Simplest way of solving your problem, have a fixed tab at the front of the file, which contains on list of each of the tabs in the workbook, you can read that and then process the other tabs.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.