when use proc import how to judge a sheet is exist or not?

[zhao]

Hi reader, i'm in trouble for below question, thanks for your help. before import a excel file into SAS, i hope to add a "if...then do" sentence, to show when a sheet name is not exist then put a warning in log, do you know how to do with that? the sheet name as a macro variable in code, that excel file is xlsx format. proc import datafile="&p_com" out=&chknm._com dbms=xlsx replace; range="&chknm.$A&cell:Z100000"; getnames=YES; run; for example if &chknm is check1, but this is not in excel, then will be an error: Couldn't find sheet in spreadsheet Requested Input File Is Invalid ERROR: Import unsuccessful. See SAS Log for details.

[RW9]

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.

[zhao]

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.

[RW9]

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.