hi I have a excel file inside is a pivot table tab with a data dump which feeds the pivot table
ccurrently I'm using
proc export data = report
outfile= " c:\report.xlsx"
dbms=excel replace;
sheet="data_dump";
run;
it replaces the whole file I need it to just replace the tab so the pivot table can refresh with new data on open thanks again
It maybe engine depended, but for the PCFILES engine, I can't seem to replace the tab directly, rather, I can delete it then make a new one.
libname xl PCFILES path="C:\temp\_test.xls";
data xl.class;
set sashelp.class;
run;
data xl.cars;
set sashelp.cars;
run;
quit;
libname xl clear;
libname xl PCFILES path="C:\temp\_test.xls" AUTOCOMMIT=YES;
proc datasets library=xl;
delete cars;
quit;
data xl.cars;
set sashelp.class;
run;
libname xl clear;
I have 32-bit MS office products and SAS 64-bit coexisting, so I can't test EXCEL engine.
Haikuo
It maybe engine depended, but for the PCFILES engine, I can't seem to replace the tab directly, rather, I can delete it then make a new one.
libname xl PCFILES path="C:\temp\_test.xls";
data xl.class;
set sashelp.class;
run;
data xl.cars;
set sashelp.cars;
run;
quit;
libname xl clear;
libname xl PCFILES path="C:\temp\_test.xls" AUTOCOMMIT=YES;
proc datasets library=xl;
delete cars;
quit;
data xl.cars;
set sashelp.class;
run;
libname xl clear;
I have 32-bit MS office products and SAS 64-bit coexisting, so I can't test EXCEL engine.
Haikuo
I Tried something like that and it worked but the tab doesn't exist it places the data right next to the pivot table.. When the tab is in the file I get and error message of it can't replace because a tab already exist...I think I will delete tab first an then paste
If you can use DBMS=XLSX and you have SAS 9.4, then you can replace an existing sheet without replacing the entire workbook/file. See an example here:
http://blogs.sas.com/content/sasdummy/2014/09/21/ods-excel-and-proc-export-xlsx/
Chris
Can you post an example workbook and an example SAS dataset that you'd like to upload?
IF your question is why the only excel (xls or zipped xml-s xlsx) is being replaced, well it is only one file on the Windows environment. There are segregated parts.
As there are no segregate parts how would you handle them separately?
This is also the root cause for locking issues. Excel is not a DBMS or really designed for that.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.