Export to Multiple Existing Excel Files as New Sheets

ark123 — Fri, 10 Mar 2023 18:46:15 GMT

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;

Re: Export to Multiple Existing Excel Files as New Sheets

PaigeMiller — Fri, 10 Mar 2023 18:50:23 GMT

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

Re: Export to Multiple Existing Excel Files as New Sheets

ark123 — Fri, 10 Mar 2023 20:07:34 GMT

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

Re: Export to Multiple Existing Excel Files as New Sheets

ChrisHemedinger — Fri, 10 Mar 2023 20:10:20 GMT

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.

Re: Export to Multiple Existing Excel Files as New Sheets

ark123 — Fri, 10 Mar 2023 20:52:20 GMT

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

topic Re: Export to Multiple Existing Excel Files as New Sheets in SAS Programming

Export to Multiple Existing Excel Files as New Sheets

Re: Export to Multiple Existing Excel Files as New Sheets

Re: Export to Multiple Existing Excel Files as New Sheets

Re: Export to Multiple Existing Excel Files as New Sheets

Re: Export to Multiple Existing Excel Files as New Sheets