Exporting multiple SAS Datasets (3 datasets) in sheet1 and 2 SAS datasets in Sheet2 in same excel

[chapidi99]

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;

[PaigeMiller]

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

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

[chapidi99]

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

[PaigeMiller]

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?

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?

[ballardw · Accepted solution]

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]

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.