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!
CF
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;
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;
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.