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

[jlam2]

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;

[Reeza]

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;

---