excel engine - replace the content of a sheet created using ods excel

[xxformat_com]

Hi,

I know that there is no issue with the xlsx engine but I was wondering on whether there is a solution with the excel engine.

Here is the issue:

When creating an Excel file with the excel engine, it is possible to replace the content of a sheet with some new data, if and only if the content of the sheet is first removed.

When creating an Excel file with ods xlsx, it is possible to remove the data (not the style) using proc datasets for example. But it is not possible to add data in this excel tab using the excel engine.

Question 1. Is there a way to remove everything from the sheet (both data and style).

Question 2. Is it possible to replace the content of an excel sheet using the excel engine when the original file was created using ods excel?

*--------------------------------------------------------------------;

ods excel file="&test./v1.xlsx" options(sheet_name='Class');

proc print data=sashelp.class noobs;
run;

ods excel close;

*--------------------------------------------------------------------;

libname test excel "&test/v1.xlsx";

proc datasets memtype=data lib=test nolist nodetails;
    delete 'Class$'n;  
    run;
quit;

data test.'Class$'n;
    set sashelp.class (where=(sex='F'));
run;

libname test;

[SASKiwi]

It is important to remember that the LIBNAME EXCEL engine and the ODS EXCEL file format are not functionally equivalent. The former uses the Microsoft ACE/Jet engine to exchange data with Excel workbooks. The latter writes XLSX-formatted files from scratch that Excel can interpret as a workbook.

So the LIBNAME EXCEL engine is limited in its ability to manipulate spreadsheets whereas ODS EXCEL provides much more formatting capability.

What happens when you run your test program?

[xxformat_com]

When I empty the sheet I get

When we try to add some data in the "empty" tab, we get the following error message:

76 data test.'Class$'n;
77 set sashelp.class (where=(sex='F'));
78 run;

ERROR: The MS Excel table Class$ 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.

[Vince_SAS]

You might not be able to update the file using the EXCEL engine because of this:

This engine does not support the REPLACE option.

Can you use the XSLX engine?

options validvarname=any validmemname=extend;

ods _all_ close;

ods Excel file='C:\temp\class.xlsx' options(sheet_name='Class');
  proc print data=sashelp.class noobs; run; quit;
ods Excel close;

libname test xlsx 'C:\temp\class.xlsx';

data test.Class;
set sashelp.class(where=(sex='F'));
run;

Vince DelGobbo

SAS R&D