topic Re: Check if a worksheet exists in an excel file in Microsoft Integration with SAS

Check if a worksheet exists in an excel file

Florent — Wed, 01 Jul 2009 10:31:51 GMT

Hello,

Does anyone know if it's easily possible to check for the existence of a worksheet in an Excel file based on its name ?

I'd like to perform that check before executing each "Proc import" statement in order not to have any error message in my log file when the specified worksheet does not exist.

Thank you in advance for your help. 🙂

Regards,
Florent

Note: the SAS version we use at the customer is the 8.02

Re: Check if a worksheet exists in an excel file

advoss — Thu, 02 Jul 2009 13:31:34 GMT

Try using the LIBNAME EXCEL engine (this might be a more convenient option than IMPORT, anyway) like:

52 libname excel excel "&my_docs\excel\111temp.xls";
NOTE: Libref EXCEL was successfully assigned as follows:
Engine: EXCEL
Physical Name: \\company.com\users\bldg\userid\My_Documents\excel\111temp.xls
53 proc sql noprint;
54 create table excel1 as
55 select libname,memname
56 from dictionary.tables
57 where libname = 'EXCEL';
NOTE: Table WORK.EXCEL1 created, with 2 rows and 2 columns.

58 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.04 seconds
cpu time 0.03 seconds

59 data _null_;
60 set excel1;
61 put _all_;
62 run;

libname=EXCEL memname=Sheet1$ _ERROR_=0 _N_=1
libname=EXCEL memname=Sheet2$ _ERROR_=0 _N_=2
NOTE: There were 2 observations read from the data set WORK.EXCEL1.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

63 libname excel clear;
NOTE: Libref EXCEL has been deassigned.

Re: Check if a worksheet exists in an excel file

Florent — Fri, 03 Jul 2009 13:09:50 GMT

Hi Advoss,

Thank you for the answer/example.

The problem is that I'm not able to use the Excel engine as the customer doesn't own the license (and they don't want it). Thus I have to use the "proc import" facility.

I know very well how to use this statement but unfortunately it gives an error while trying to import a worksheet which doesn't exist in the Excel file. This is why I created this post: to ask the SAS experts if there is a way to check the existence of a worksheet's name before executing the import of data.

Thanks again for your help.

Regards,
Florent

Re: Check if a worksheet exists in an excel file

sbb — Fri, 03 Jul 2009 13:21:23 GMT

Share the code you are using and the exact SAS error from the log - that will help with debugging your problem.

Scott Barry
SBBWorks, Inc.

Re: Check if a worksheet exists in an excel file

Florent — Fri, 03 Jul 2009 16:03:59 GMT

Here it is. This is a typical error message when the sheet you try to import does not exist in the Excel file.

MPRINT(MAIN): ;
MPRINT(IMPORT_SHEET): PROC IMPORT DATAFILE=
"W:\04_0004\06_PRICING_CONTRACTING\05_STANDARDISED_ELEC\08_Airport_GUI\01_Dossiers\0100000-0120000\100002_TESTS_BETA_COPY\2009\SCEN_PTC01_090611-1710_0702-1348.XLS" OUT= efm_input_month_MV
DBMS=EXCEL2000 REPLACE;
MPRINT(IMPORT_SHEET): AEXC;
MPRINT(IMPORT_SHEET): SHEET="Beta_INS_MV";
MPRINT(IMPORT_SHEET): GETNAMES=YES;
MPRINT(IMPORT_SHEET): RUN;

ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used:
real time 0.17 seconds
cpu time 0.07 seconds

Re: Check if a worksheet exists in an excel file

sbb — Fri, 03 Jul 2009 17:15:01 GMT

If you are getting a syntax error when the sheet does not exist, I would consider this an item for SAS tech support and recommend you open a track on the support site. There should be some type of option you can set to control the level of error/warning raised in this condition.

Scott Barry
SBBWorks, Inc.

Re: Check if a worksheet exists in an excel file

data_null__ — Mon, 20 Jul 2009 13:50:04 GMT

You may be able to use this program or some variation. It creates a SAS data set of the name of all sheet in the XLS specified by the WORKBOOK fileref.

[pre]
filename workbook "path to xls of interest";
data work.sheets;
length path script filevar command $256;
path = pathname('WORKBOOK');
script = catx('\',pathname('WORK'),'SHEETNAMESS.vbs');
filevar = script;

/* write the script */
file dummy1 filevar=filevar;

put 'Const ' path=:$quote256.;
put 'Set objExcel = CreateObject("Excel.Application")';
put 'With objExcel';
put +3 '.Visible = False';
put +3 '.DisplayAlerts = False';
put +3 'Set objWorkbook = .Workbooks.Open(path)';
put +3 'Set colSheets = .Worksheets';
put +3 'For Each objSheet In colSheets';
put +6 'WScript.echo objsheet.name';
put +6 'Next';
put +3 '.Application.Quit';
put +3 'End With';

/* close the script file by opening another, not used */
filevar = catx('\',pathname('WORK'),'DUMMY.vbs');
file dummy1 filevar=filevar;

/* look at the script, not necessary but may be useful */
infile dummy2 filevar=script end=eof;
do _n_ = 1 by 1 until(eof);
input;
putlog _n_ z3. +1 _infile_;
end;

/* call the script */
command = catx(' ','cscript //nologo',quote(strip(script)));
infile dummy3 pipe filevar=command end=eof length=l;
do until(eof);
input sheet $varying256. l;
output;
putlog _infile_;
end;
stop;
run;

proc contents order=varnum;
proc print;
run;
[/pre]

Re: Check if a worksheet exists in an excel file

Peter_C — Tue, 21 Jul 2009 20:42:39 GMT

Florent

as you may have found out by now, if your client does not license SAS/Access(r) Interface to PC File Formats (which would enable the excel libname engine), then it is very unlikely that you can run proc import with any variant of dbms=excel

unless you know different

PeterC

Re: Check if a worksheet exists in an excel file

Florent — Wed, 22 Jul 2009 12:23:55 GMT

data _null_;,

After having made some changes (related to the SAS version that we use at the customer), I've tried to execute your example but it gives the following error.

Would it be possible that I have this error message because of the SAS version ? What could I use in order to get the same result ? Could you briefly explain me the aim of that 'pipe' option (I'm not used to such customization of the infile statement) ?

PeterC,

I've tried with other variants of the dbms option but this does not change anything.
I still have an error message in the logfile if the sheet that I try to import does not exist in the Excel file.

Thanks to both of you for your time.

Kind Regards,
Florent

441 filename workbook "...\SCEN_PTC01_090611-1710_0714-1424.xls";

442 data work.sheets;
443 length path script filevar command $256;
444 path = pathname('WORKBOOK');
445 script = pathname('WORK')||'\SHEETNAMESS.vbs';
446 filevar = script;
447
448 /* write the script */
449 file dummy1 filevar=filevar;
450
451 put 'Const ' path=:$quote256.;
452 put 'Set objExcel = CreateObject("Excel.Application")';
453 put 'With objExcel';
454 put +3 '.Visible = False';
455 put +3 '.DisplayAlerts = False';
456 put +3 'Set objWorkbook = .Workbooks.Open(path)';
457 put +3 'Set colSheets = .Worksheets';
458 put +3 'For Each objSheet In colSheets';
459 put +6 'WScript.echo objsheet.name';
460 put +6 'Next';
461 put +3 '.Application.Quit';
462 put +3 'End With';
463
464 /* close the script file by opening another, not used */
465 filevar = pathname('WORK')||'DUMMY.vbs';
466 file dummy1 filevar=filevar;
467
468 /* look at the script, not necessary but may be useful */
469 infile dummy2 filevar=script end=eof;
470 do _n_ = 1 by 1 until(eof);
471 input;
472 put _n_ z3. +1 _infile_;
473 end;
474
475 /* call the script */
476 command = 'cscript //nologo '||quote(trimn(left(script)));
NOTE: SCL source line.
477 infile dummy3 pipe filevar=command end=eof length=l;
----
23
ERROR 23-2: Invalid option name PIPE.

478 do until(eof);
479 input sheet $varying256. l;
480 output;
481 put _infile_;
482 end;
483 stop;
484 run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.SHEETS may be incomplete. When this step was stopped there were 0 observations and 2 variables.
WARNING: Data set WORK.SHEETS was not replaced because this step was stopped.
NOTE: DATA statement used:
real time 0.01 seconds
cpu time 0.01 seconds

Re: Check if a worksheet exists in an excel file

data_null__ — Wed, 22 Jul 2009 16:07:11 GMT

This program works with SAS 9.1.3 on Windows. I don't know if it works in version 8.

If PIPE does not work with FILEVAR in version 8 you can do the same thing with multiple data steps.