Help using Base SAS procedures

Replace excel tab but not the file

Accepted Solution Solved
Reply
Regular Contributor
Posts: 240
Accepted Solution

Replace excel tab but not the file

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


Accepted Solutions
Solution
‎02-24-2015 10:53 PM
Respected Advisor
Posts: 3,156

Re: Replace excel tab but not the file

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

View solution in original post


All Replies
Solution
‎02-24-2015 10:53 PM
Respected Advisor
Posts: 3,156

Re: Replace excel tab but not the file

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

Regular Contributor
Posts: 240

Re: Replace excel tab but not the file

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

Community Manager
Posts: 2,953

Re: Replace excel tab but not the file

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

PROC Star
Posts: 7,471

Re: Replace excel tab but not the file

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

Trusted Advisor
Posts: 3,212

Re: Replace excel tab but not the file

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.

---->-- ja karman --<-----
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 777 views
  • 3 likes
  • 5 in conversation