02-10-2016 12:07 PM
I'm trying to find a way to conditionally execute statements based on the presence of a sheet name in a workbook.
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.
02-10-2016 01:00 PM
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.
02-10-2016 01:06 PM
02-10-2016 01:03 PM - edited 02-10-2016 01:08 PM
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;