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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.