topic Re: Replace and Export Tab in Excel (2013) using SAS in SAS Programming

Replace and Export Tab in Excel (2013) using SAS

camfarrell25 — Fri, 06 Jan 2017 03:44:07 GMT

Hello,

Been trying to replace/delete a sheet in excel (2013 - xslx) and export a dataset to the same workbook, although I seem to be hitting road blocks when it comes to directly modiying the excel spreadsheet.

I've managed to set the libname:

libname xls xlsx "(Excel FilePath.xlsx)";

but I'm not able to modify the workbook directly from SAS.

I've seen some talk about using DDE but if there's a simple solution out there I would appreciate it.

Any ideas??

Thanks!

Re: Replace and Export Tab in Excel (2013) using SAS

Ksharp — Fri, 06 Jan 2017 10:37:05 GMT

Drop it firstly, before export it.


libname xls xlsx "(Excel FilePath.xlsx)";

proc sql;
drop table xls.have;

create table xls.have as 
select * from have;
quit;

Re: Replace and Export Tab in Excel (2013) using SAS

SASKiwi — Fri, 06 Jan 2017 06:21:36 GMT

In addition to @Ksharp's method, I use PROC DATASETS to delete a sheet then add it again:

proc datasets library = xls;
  delete MySheet;
  run;
  copy in = WORK out = xls;
  select MySheet;
  run;
quit;

Re: Replace and Export Tab in Excel (2013) using SAS

camfarrell25 — Fri, 06 Jan 2017 13:33:30 GMT

When using that code, I get the following error message:

ERROR: XLS.DATA.DATA cannot be deleted because files cannot be deleted from the XLS library.