BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BETO
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

5 REPLIES 5
Haikuo
Onyx | Level 15

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

BETO
Fluorite | Level 6

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

ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
art297
Opal | Level 21

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

jakarman
Barite | Level 11

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 --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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