Export to Multiple Existing Excel Files as New Sheets

[ark123]

I have multiple excel files saved. Each excel file is for an individual location and each has one sheet. I have additional data that I would like to add to each of these files. I am trying to add this data as an additional sheet to each of my existing excel files, again based on location.

So for example, one excel file might be named 'New York' with one tab named 'New York' and another might be an excel file named 'Michigan' with one tab named 'Michigan'. I would like to add an additional tab to each titled 'Missing' (if possible).

The code I am sharing produces a new excel file with each location in a separate tab. Instead of this output, is there a way to assign each tab to the existing excel files I have saved based on location (so if this output below has a tab 'New York' it would save as a new sheet title 'Missing' to the 'New York' excel file)?

ods excel file = "FILEPATH\Test.xlsx";	
ods excel options (sheet_interval="bygroup" sheet_name="#byval(Location)");

proc sort data = test;
	by location;
run;

options nobyline;
proc print data = test noobs;
	by location;
	var Patient_ID / style(data)=[fontfamily='calibri' fontsize=11pt foreground=black background=WHITE] style(header)=[fontfamily='calibri' fontsize=11pt foreground=black background=WHITE];
	var LastName / style(data)=[fontfamily='calibri' fontsize=11pt foreground=black background=PAP] style(header)=[fontfamily='calibri' fontsize=11pt foreground=black background=PAP];
	var FirstName / style(data)=[fontfamily='calibri' fontsize=11pt foreground=black background=PAP] style(header)=[fontfamily='calibri' fontsize=11pt foreground=black background=PAP];
	var DOB / style(data)=[fontfamily='calibri' fontsize=11pt foreground=black background=PAP] style(header)=[fontfamily='calibri' fontsize=11pt foreground=black background=PAP];
	var location / style(data)=[fontfamily='calibri' fontsize=11pt foreground=black background=VLIGB] style(header)=[fontfamily='calibri' fontsize=11pt foreground=black background=VLIGB];
run;
options byline;
ods excel close;

[PaigeMiller]

ODS EXCEL cannot add sheets into existing Excel documents. It always creates a new Excel file.

[ark123]

Thank you for sharing that. Do you know of another way that this could be done?

[ChrisHemedinger · Accepted solution]

ODS EXCEL always creates a new file. 

PROC EXPORT can add a new sheet to an existing workbook. See examples here. But PROC EXPORT exports just data, not formatting/graphics like ODS EXCEL can do.

[ark123]

Thank you for your feedback. I'll stick with my ods excel export and move the tabs in excel.