topic Export to excel with color on multiple sheet in ODS and Base Reporting

Export to excel with color on multiple sheet

hhchenfx — Wed, 16 Jan 2019 02:46:55 GMT

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;

Re: Export to excel with color on multiple sheet

Reeza — Wed, 16 Jan 2019 04:34:54 GMT

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,