topic Re: Replace excel tab but not the file in SAS Procedures

Replace excel tab but not the file

BETO — Wed, 25 Feb 2015 02:55:22 GMT

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

Re: Replace excel tab but not the file

Haikuo — Wed, 25 Feb 2015 03:53:22 GMT

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

Re: Replace excel tab but not the file

BETO — Wed, 25 Feb 2015 15:27:35 GMT

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

Re: Replace excel tab but not the file

art297 — Wed, 25 Feb 2015 17:10:09 GMT

Can you post an example workbook and an example SAS dataset that you'd like to upload?

Re: Replace excel tab but not the file

ChrisHemedinger — Wed, 25 Feb 2015 17:40:56 GMT

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

Re: Replace excel tab but not the file

jakarman — Wed, 25 Feb 2015 20:16:39 GMT

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.