The SAS Output Delivery System and reporting techniques

ODS EXCEL Single Sheet with By Groups

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

ODS EXCEL Single Sheet with By Groups

[ Edited ]

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!


Accepted Solutions
Solution
‎12-30-2016 04:59 PM
Super User
Posts: 19,105

Re: ODS EXCEL Single Sheet with By Groups

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...

View solution in original post


All Replies
Solution
‎12-30-2016 04:59 PM
Super User
Posts: 19,105

Re: ODS EXCEL Single Sheet with By Groups

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...

Occasional Contributor
Posts: 5

Re: ODS EXCEL Single Sheet with By Groups

Thanks - that worked to get me onto one sheet. Now I just have to get the sums in the right place.
Thanks!
Super User
Posts: 19,105

Re: ODS EXCEL Single Sheet with By Groups

You should consider PROC tabulate. 

SAS Super FREQ
Posts: 8,819

Re: ODS EXCEL Single Sheet with By Groups

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

cynthia
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 308 views
  • 0 likes
  • 3 in conversation