ODS EXCEL Single Sheet with By Groups

[lisagsmi1]

How can I send a PROC PRINT that has BY Groups to ODS EXCEL and have it stay on one sheet?

I need to create an Excel report that looks like a PROC PRINT with By Groups, and a Sum. 

Input SAS dataset looks like this: 

Region               State          Count

------------------------------------------------

EASTERN          GA             5

EASTERN          FL              4

EASTERN          AL              3

WESTERN         CA             7

WESTERN         OR             8

WESTERN         WA             9

Spreadsheet needs to look like this:

Region               State          Count

------------------------------------------------

EASTERN          GA             5

                           FL              4

                           AL              3

WESTERN         CA             7

                           OR             8

                           WA             9

--------------------------------------------

Grand Total                          36

I need the BY and ID in the following code to get the region to look right, but using the BY causes multiple sheets. 

(SAS Release 9.04.01M2P072314)

ods excel file="&OUTPUT_PATH.\&OUTPUT_FILE._&yyyymmdd..xlsx"
options ( /* Display options */
	sheet_name="OUTPUT_RPT"
	absolute_column_width='40,6,7'
	embedded_titles='on')
	;
run;

proc print data=cons_rpt noobs  sumlabel label;
	var
		Region
		State      
		Count 		; 
	by Region;
	id Region;
	label Region='Grand Total'
	;
	sum count;

run;
ods excel close;

run;

(Please don't make me use Tagsets.ExcelXP)

Thanks!

[Reeza · Accepted solution]

Try the SHEET_INTERVAL option in your options list, set it to none. 

Sheet_Interval='none'

http://support.sas.com/documentation/cdl/en/odsug/69832/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04...

[lisagsmi1]

Thanks - that worked to get me onto one sheet. Now I just have to get the sums in the right place.
Thanks!

[Reeza]

You should consider PROC tabulate. 

[Cynthia_sas]

Or PROC REPORT.... but still PROC PRINT should be able to get the totals in the right place for you.

cynthia