How to replace only one excel sheet and keep other sheets as is

jlam2 — Wed, 04 Aug 2021 23:02:39 GMT

Hello,

I have an excel file with multiple sheets: Sheet1, Sheet2, Sheet3, Sheet4. I want to run a weekly report that replaces information on Sheet1 but keeps Sheet2, Sheet3, and Sheet4 as is. Currently, my code below replaces Sheet1 with new information but deletes Sheets2-4 in the newly exported file. Can anyone help me figure out what I should code for this, please?

PROC EXPORT data=new
OUTFILE = "T:\filename.xlsx"
DBMS = EXCEL REPLACE;
SHEET = "Sheet1";
NEWFILE = YES;
RUN;

Re: How to replace only one excel sheet and keep other sheets as is

Reeza — Wed, 04 Aug 2021 23:07:59 GMT

Have you tried using a LIBNAME approach instead? I noticed you're using DBMS=EXCEL which is really old, if you're on an older version of SAS this may not work.

libname myFile xlsx 'path to xlsx file';

proc sql;
drop table myFile.Sheet1;
quit;

data myFile.Sheet1;
set new;
run;

libname myFile;

@jlam2 wrote:

Hello,

I have an excel file with multiple sheets: Sheet1, Sheet2, Sheet3, Sheet4. I want to run a weekly report that replaces information on Sheet1 but keeps Sheet2, Sheet3, and Sheet4 as is. Currently, my code below replaces Sheet1 with new information but deletes Sheets2-4 in the newly exported file. Can anyone help me figure out what I should code for this, please?

PROC EXPORT data=new
OUTFILE = "T:\filename.xlsx"
DBMS = EXCEL REPLACE;
SHEET = "Sheet1";
NEWFILE = YES;
RUN;

topic How to replace only one excel sheet and keep other sheets as is in SAS Programming

How to replace only one excel sheet and keep other sheets as is

Re: How to replace only one excel sheet and keep other sheets as is