How to use SAS/SQL to count the number of EXCEL files

Suppose there are multiple EXCEL files under a file path. All the files have irregular names. Each file has multiple sheets, also irregularly named. Now I want to use PROC SQL to count the number of EXCEL files under the file path, and the number of sheets in each different EXCEL file. How to do it? Thank you so much!

Re: How to use SAS/SQL to count the number of EXCEL files

If you have the SAS product SAS/ACCESS to PC Files then it is possible to set up a LIBNAME to each Excel workbook and count up the number of sheets using the SAS DICTIONARY table MEMBERS.

It would take quite a bit of coding to get it working and it would ignore sheet names longer than 32 characters because that is the maximum SAS can handle via the DICTIONARY mechanism. I would probably take quite a while to run also if you have a lot of workbooks.

Re: How to use SAS/SQL to count the number of EXCEL files

If you have the SAS product SAS/ACCESS to PC Files then it is possible to set up a LIBNAME to each Excel workbook and count up the number of sheets using the SAS DICTIONARY table MEMBERS.

It would take quite a bit of coding to get it working and it would ignore sheet names longer than 32 characters because that is the maximum SAS can handle via the DICTIONARY mechanism. I would probably take quite a while to run also if you have a lot of workbooks.

Re: How to use SAS/SQL to count the number of EXCEL files

Sorry, I didn't read that about Sheet names. I would suggest the easiest way to do this would be to use VBA in Excel - use the right tech for the task. It is a simple bit of code, outside of the scope of this forum however. But google for:

You then have a list of files, with number of sheets. You could also output a list of sheet names if you. VBA is pretty powerfull when using Excel. Just google anything VBA excel <your topic> and there is lots of code available.