Check Excel workbooks for specific Sheets, import irregular data

[nellisct]

I'm trying to find a way to conditionally execute statements based on the presence of a sheet name in a workbook.  

For example: 

proc import DATAFILE="filename.xlsx" OUT=SasDataSet
DBMS=xlsx;
SHEET="obscureSheetName";
run;

That code will run and create the dataset if the sheet exists, but if it doesn't it'll throw an error ("ERROR: Couldn't find sheet in spreadsheet")

If there's some way to catch that error and execute code on that, or perhaps a way to execute code based on if the import happened successfully, I'd love to hear it.

[data_null__]

I think the answer will depend on the version of SAS and if you have access to pc files software and your operating system.

[nellisct]

Oh, sorry for not being more clear!

I'm running SAS 9.4 on Win7 Pro (64bit). If there's anything else I can post to help, let me know

[RW9]

If the files are XLSX then you could libname directly to the file.  This will create a libname with each valid sheet as a dataset.  However the question I would ask is why you are doing this?  Do you not know what data you will get, and if so, how will you program to handle it.  Now I know that sometimes it is not possible to do things correctly, but you must have some control over the data you are getting, even saving the data you want out to CSV and then importing that.  

[nellisct]

Thanks for your reply. I'm constructing a background task using SAS. The use case is that there's a folder with lots of files in it, some of them Excel files. Only one of them needs to be processed, but instead of using the file name to identify the file (which would make much more sense), the solution we're using is to only import data from a workbook that has a specific spreadsheet name. That's what I'm working on, something of a prototype to see if it's a viable solution. I'm not actually working on importing the data, just controlling the workflow to make sure the right file is selected for whatever operations will happen next

[data_null__]

If you can use the XLSX library engine.

libname xlslib xlsx ‘<Full path and filename of xlsx spreadsheet>’;

if this LIBREF works then you can treat the workbook more or less like a data libraray Start by running 

PROC DATASETS LIB=XLSLIB;

[nellisct]

That's super helpful! Thanks! I'll investigation this as a solution