BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ark123
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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.

 

 

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
ark123
Obsidian | Level 7

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

ChrisHemedinger
Community Manager

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.

 

 

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
ark123
Obsidian | Level 7

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 848 views
  • 0 likes
  • 3 in conversation