BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chapidi99
Fluorite | Level 6

Hello,

 

I am trying to export 3 SAS datasets in sheet1 and 2 SAS datasets in sheet2 of the same excel dataset. But when I run my SAS program it overwrite the shee1 wit sheet2. Can anyone help me to fix this issue?

 

ods tagsets.excelxp file="C:\Users\Reports_Final Calculations\Semesterjahr.xls"
options(embedded_titles='yes' sheet_interval='NONE' sheet_name='sheet1');
proc print data=work.Summ_Kosten noobs label;
title "Total Cost";
run;

proc print data=Kosten_Pro_Jahr_Final noobs label;
title "Total Cost pro Jahr";

run;
proc print data=Kosten_Pro_Sem_Final noobs label;
title "Total Cost pro Semester";

run;

proc print data=Kosten_Pro_WS_SS_Final noobs label;
title "Summer and Winter Semesters";

run;
ods excel close;

 

 

/* Kosten_Pro_Jahr_Ohne_Pat_Final */
/* Kosten_60_300_WS_SS_Pat_Final */

 

 

ods tagsets.excelxp file="C:\Users\\Reports_Final Calculations\Kosten je Semesterjahr.xls"
options(embedded_titles='yes' sheet_interval='NONE' sheet_name='Sheet2');
proc print data=work.Summ_Kosten_UEs_Sem noobs label;
title "UE je Format je Semester";
run;

proc print data=Append_All_Jahr_Final_UE noobs label;
title "UE je Kalenderjahr";

run;

ods excel close;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

ALL output going to single ODS statement must be in the same ODS <destination> / ODS <destination> close.

When specify the same file on a different ODS statement that is what it is supposed to do: start a new file.

I think what you want is to force a new sheet with options. Note: Your code doesn't run correctly as posted because you are using the wrong ODS CLOSE. Your file is ODS EXCELXP and your code closes the ODS EXCEL destination which is not the same thing.

 

Maybe:

ods tagsets.excelxp file="C:\Users\Reports_Final Calculations\Semesterjahr.xls"
options(embedded_titles='yes' sheet_interval='NONE' sheet_name='sheet1');
proc print data=work.Summ_Kosten noobs label;
title "Total Cost";
run;

proc print data=Kosten_Pro_Jahr_Final noobs label;
title "Total Cost pro Jahr";

run;
proc print data=Kosten_Pro_Sem_Final noobs label;
title "Total Cost pro Semester";

run;

proc print data=Kosten_Pro_WS_SS_Final noobs label;
title "Summer and Winter Semesters";

run;
/* hopefully starts a new sheet */
ods tagsets.excelxp options (sheet_interval='PAGE'); 

/* and this resets so the next two proc print output goes to the same
  sheet*/
ods tagsets.excelxp 
options( sheet_interval='NONE' sheet_name='Sheet2');
proc print data=work.Summ_Kosten_UEs_Sem noobs label;
title "UE je Format je Semester";
run;

proc print data=Append_All_Jahr_Final_UE noobs label;
title "UE je Kalenderjahr";

run;

ods excelxp close;

Note that lying to applications with the wrong file type such as XLS for XML may cause problems with some software opening such as the contents are not actual XLS.

 

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26

In the 2nd ODS EXCEL, try the option SHEET_INTERVAL='NOW'

 

(works in SAS 9.4M3 and later. I think)

--
Paige Miller
chapidi99
Fluorite | Level 6

It still overwrites the sheet with 1st ods tables and exports 1st and 2nd tables in a separate sheets.

PaigeMiller
Diamond | Level 26

What version NUMBER of SAS are you using? From Help->About SAS 9, this is what I see, so I have SAS 9.4 TS Level 1M5. What do you see?

 

PaigeMiller_0-1647537952637.png

 

Also, you can't just say "it doesn't work" and give us no other explanation. We need to know what you did. SHOW US the code. And, are there errors in the log?

--
Paige Miller
ballardw
Super User

ALL output going to single ODS statement must be in the same ODS <destination> / ODS <destination> close.

When specify the same file on a different ODS statement that is what it is supposed to do: start a new file.

I think what you want is to force a new sheet with options. Note: Your code doesn't run correctly as posted because you are using the wrong ODS CLOSE. Your file is ODS EXCELXP and your code closes the ODS EXCEL destination which is not the same thing.

 

Maybe:

ods tagsets.excelxp file="C:\Users\Reports_Final Calculations\Semesterjahr.xls"
options(embedded_titles='yes' sheet_interval='NONE' sheet_name='sheet1');
proc print data=work.Summ_Kosten noobs label;
title "Total Cost";
run;

proc print data=Kosten_Pro_Jahr_Final noobs label;
title "Total Cost pro Jahr";

run;
proc print data=Kosten_Pro_Sem_Final noobs label;
title "Total Cost pro Semester";

run;

proc print data=Kosten_Pro_WS_SS_Final noobs label;
title "Summer and Winter Semesters";

run;
/* hopefully starts a new sheet */
ods tagsets.excelxp options (sheet_interval='PAGE'); 

/* and this resets so the next two proc print output goes to the same
  sheet*/
ods tagsets.excelxp 
options( sheet_interval='NONE' sheet_name='Sheet2');
proc print data=work.Summ_Kosten_UEs_Sem noobs label;
title "UE je Format je Semester";
run;

proc print data=Append_All_Jahr_Final_UE noobs label;
title "UE je Kalenderjahr";

run;

ods excelxp close;

Note that lying to applications with the wrong file type such as XLS for XML may cause problems with some software opening such as the contents are not actual XLS.

 

chapidi99
Fluorite | Level 6

Perfect! it works really well. Yes I realise now, I was using wrong ods to close and excel destination. Thank you for the perfect explanation. 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 486 views
  • 1 like
  • 3 in conversation