Export to excel with color on multiple sheet

[hhchenfx]

Hi,

I try to export data to Excel with color using the following code

However, it only export the last sheet.

Also, how to add REPLACE option as in proc export?

Any help is very much appreciated.

HHCFX

ods listing close;
ods excel file="C:\Users\_temp\exp.xlsx"   
                    options (sheet_interval = "none" sheet_name = "SHEET1" );

		proc report data=sashelp.class nowd;
		compute name;
		call define (_col_,"style","style={background=lightgray}");
			endcomp;
		run;

ods excel close;


ods listing close;
ods excel file="C:\Users\_temp\exp.xlsx"   
                    options (sheet_interval = "none" sheet_name = "SHEET2" );

		proc report data=sashelp.class nowd;
		compute Weight;
		call define (_col_,"style","style={background=lightgray}");
			endcomp;
		run;

ods excel close;

[Reeza · Accepted solution]

Because you have to ODS EXCEL calls it overwrites the previous results with just the final results. If you want the output into two sheets in the same workbook you can simply not close the destination between PROCS.

ods listing close;
ods excel file="/folders/myfolders/exp.xlsx"   
                    options (sheet_interval = "proc" sheet_name = "SHEET1" );

        proc report data=sashelp.class nowd;
        compute name;
        call define (_col_,"style","style={background=lightgray}");
            endcomp;
        run;


ods excel  options (sheet_interval = "proc" sheet_name = "SHEET2" );

        proc report data=sashelp.class nowd;
        compute Weight;
        call define (_col_,"style","style={background=lightgray}");
            endcomp;
        run;

ods excel close;

AFAIK there is no REPLACE option specifically, but appears to be the default. You cannot append ODS EXCEL data to an existing Excel file or template.

---

@hhchenfx wrote:

Hi,

I try to export data to Excel with color using the following code

However, it only export the last sheet.

Also, how to add REPLACE option as in proc export?

Any help is very much appreciated.

HHCFX

ods listing close;
ods excel file="C:\Users\_temp\exp.xlsx"   
                    options (sheet_interval = "none" sheet_name = "SHEET1" );

		proc report data=sashelp.class nowd;
		compute name;
		call define (_col_,"style","style={background=lightgray}");
			endcomp;
		run;

ods excel close;


ods listing close;
ods excel file="C:\Users\_temp\exp.xlsx"   
                    options (sheet_interval = "none" sheet_name = "SHEET2" );

		proc report data=sashelp.class nowd;
		compute Weight;
		call define (_col_,"style","style={background=lightgray}");
			endcomp;
		run;

ods excel close;

---

[hhchenfx]

Thank you,

HHC