ODS EXCEL Single Sheet with By Groups

lisagsmi1 — Fri, 30 Dec 2016 21:37:31 GMT

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!

Re: ODS EXCEL Single Sheet with By Groups

Reeza — Fri, 30 Dec 2016 21:21:18 GMT

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#p09n5pw9ol0897n1qe04zeur27rv.htm

Re: ODS EXCEL Single Sheet with By Groups

lisagsmi1 — Fri, 30 Dec 2016 22:00:03 GMT

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

Re: ODS EXCEL Single Sheet with By Groups

Reeza — Fri, 30 Dec 2016 22:35:52 GMT

You should consider PROC tabulate.

Re: ODS EXCEL Single Sheet with By Groups

Cynthia_sas — Sat, 31 Dec 2016 02:53:28 GMT

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

cynthia

topic Re: ODS EXCEL Single Sheet with By Groups in ODS and Base Reporting

ODS EXCEL Single Sheet with By Groups

Re: ODS EXCEL Single Sheet with By Groups

Re: ODS EXCEL Single Sheet with By Groups

Re: ODS EXCEL Single Sheet with By Groups

Re: ODS EXCEL Single Sheet with By Groups