SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Error message using Excel libname engine to create tables

Reply
Occasional Contributor
Posts: 10

Error message using Excel libname engine to create tables

I'm running the below code to copy an Excel template, then replace the two tables aHeader and aException in the output file, to which I assign the libname wbkOut. When I run this, I get the following error message from the data step which creates the table aHeader:
ERROR: The MS Excel table aHeader has been opened for OUTPUT. This table already exists, or there is a name conflict with an
existing object. This table will not be replaced. This engine does not support the REPLACE option.

However, the data step creating the table aException runs fine:
There were 1305 observations read from the data set WORK.EXC_DEF_WO_LINK.
NOTE: The data set WBKOUT.aException has 1305 observations and 9 variables.

%let report = &path.\&regref.\output\DV_42_Day_Monitoring_&cms_d_fmt..xls ;
%let template = &path.\&regref.\excel\Report_Template.xls ;

filename template "&template" ;
filename outfile "&report";

data _null_;
infile template recfm=n;
input one_byte $char1. @@;
file outfile recfm=n;
put one_byte $char1. @@;
run;

libname wbkOut EXCEL "&report" ver=2002 scan_text=no;
proc datasets lib=wbkOut;
delete aHeader;
delete aException;
run;
quit;

data Headers ;
format startdate enddate refreshdate reportdate date9.;
progpath="&regref.\sasprogs\&pgmname";
startdate=&startdate.d;
enddate=&enddate.d;
refreshdate="&cms_d_fmt"d;
reportdate="&sysdate9"d;
run;

data wbkOut.aHeader ;
set Headers;
run;

data Exc_Def_WO_Link (keep=region case_current_court case_officer
case_number doc_type doc_number doc_filed_due_d
doc_id case_first_app_filed_d);
set defended_fam_wo_app_link;
case_current_court=put(case_current_court_code, $crtcs.);
case_officer=put(case_officer_id, dfcuser.);
doc_type=put(doc_type_code, $doctyp.);
label region = 'Region'
case_current_court = 'Current Court'
case_officer = 'Case Officer'
case_number = 'Case Number'
doc_type = 'Doc Type'
doc_number = 'Doc Number'
doc_filed_due_d = 'Doc Filing Date'
doc_id = 'Document Id'
case_first_app_filed_d = 'First App Filing Date';
run;

data wbkOut.aException (dblabel=yes);
set Exc_Def_WO_Link;
retain region case_current_court case_officer case_number doc_type doc_number doc_filed_due_d
doc_id case_first_app_filed_d;
run;

libname wbkOut clear;

Can anyone spot anything in here that could be causing this error? I've tried assigning the libname separately first for replacing aHeader, then clearing and assigning again to replace aException - when I did this the aHeader table was replaced ok and replacing the aException table threw an identical error. Both of these tables can be seen from the Server List in Enterprise Guide when the libname is assigned - after the above code runs wbkOut.aHeader can be seen as the 5 columns F1-F5 with 0 rows.

I'm using SAS Enterprise Guide v4.1 on Windows XP - the SAS version is 9.1 and the Excel file format is Excel 97/2003 (but the file was created using Excel 2007).
Super User
Posts: 5,388

Re: Error message using Excel libname engine to create tables

Why do you copy the Excel file using a data step?
If you can, just try to copy it using OS command.
You could also try to clear the filename outfile after the copy.
/Linus
Data never sleeps
Occasional Contributor
Posts: 10

Re: Error message using Excel libname engine to create tables

Thanks for the reply Linus. I'm using the data step because, as far as I'm aware, I'm unable to issue commands to the OS from Enterprise Guide. Having said that, I'd be delighted to be proved wrong in this - copying a file byte by byte is clunky in my opinion and it would be far easier and more convenient to be able to direct Windows to perform this step itself.

As it happens, I've got it working by changing the name of the range 'aException' to 'bException', and re-ordering the sheets in the workbook - this makes no sense to me but I'll take it. It may well help to clear the filename after the copy (which I've now done), but I suspect the problem may be something to do with interaction between SAS/Access and the JET provider. There are a couple of other defined names in the workbook which refer to formulae rather than to static ranges, and thus require evaluation by Excel before returning any values. Even though the SAS script does not refer to these names at all, in my experience JET has trouble with these sorts of names (i.e. they can interfere with JET reading other names). I'm now considering whether it might be worth redesigning the workbook so that dynamic names are no longer used.

Cheers
Geoff
Ask a Question
Discussion stats
  • 2 replies
  • 146 views
  • 0 likes
  • 2 in conversation