re: proc report totals

[twildone]

Hi...I am trying to include also two more row totals in the same summary table and the table below is what I would like to end up with. The two row totals I would like to add is under the second column, PSec - Total and under the first column, Grand Total. I have included the code that I have so far. Any suggestions....Thanks.

				AY
				2013/14		2014/15		2015/16		2016/17		2017/18		2018/19		2019/20
Sec	MHS		.		.		15		33		25		15		.
Sec	Sec		228		220		351		370		401		392		6
	Sec - Total		228		220		366		403		426		407		6
PSec	ELT	EAP	.		.		96		441		623		485		89
PSec	ELT	ESL	1385		1460		1523		1537		521		551		15
	ELT - Total		1385		1460		1619		1978		1144		1036		104
PSec	YB		.		27		50		38		34		28		.
	YB - Total		.		27		50		38		34		28		.
PSec	LLI	App	.		46		44		49		42		35		.
PSec	LLI	Other LLI	16		297		247		173		152		119		18
	LLI - Total		16		343		291		222		194		154		18
	Other PS Stud - Total		1039		1166		1409		1494		1678		1859		947
	PSec - Total		2440		2996		3369		3732		3050		3077		1069
	ALC - Total		.		1006		1095		1013		989		.		.
Grand Total			2668		4222		4830		5148		4465		3484		1075
PSec	Other PS Prog		1047		1177		1419		1610		1816		1966		949
	Other PS Prog - Total		1047		1177		1419		1610		1816		1966		949
PSec	ELT	EAP - Prog	.		.		96		989		1326		1146		89
	ELI1 - Total		.		.		96		989		1326		1146		89
PSec	ELT	ESL - Prog	1385		1460		1523		1537		521		551		15
	ELI1 - Total		1385		1460		1523		1537		521		551		15

ods _all_ close;
ods listing close;
ods noresults;

options leftmargin = .5in
			rightmargin = .5in
			topmargin =.5in
			bottommargin =.5in;
title;
 
ods excel file="%sysfunc(pathname(project))\Annual Report1 (&rundate).xlsx"  options(flow="rowheaders,text,tables");
ods excel
	options(sheet_name='EnrolmeNT' title_footnote_nobreak="no" embedded_titles='yes' absolute_column_width='20,40,20,10,10,10,10,10,10,10'  absolute_row_height='15' tab_color='LightYellow' embedded_titles='yes' suppress_bylines='yes' embedded_Footnotes = 'Yes' 
		orientation='Landscape' sheet_label=' ' sheet_interval ='proc'  page_order_across='Yes' pages_fitwidth='1' frozen_headers='on' pages_fitheight = '1' fittopage='yes'
		row_repeat = '1-2' print_footer='&C CONFIDENTIAL   Prepared By: D. Babee &RPage &P OF &N');

proc report data=Have nowd split='00'x;
  column c1 c2 c3 ('AY'('2013/14'n '2014/15'n '2015/16'n '2016/17'n '2017/18'n '2018/19'n '2019/20'n group1)) ;
  	define c1 / ' ' display;
  	define c2 / ' ' display format=$70.;  
  	define c3 / ' ' display;
  	define '2013/14'n / analysis sum order=data;
  	define '2014/15'n / analysis sum order=data;
  	define '2015/16'n / analysis sum order=data;
  	define '2016/17'n / analysis sum order=data;
  	define '2017/18'n / analysis sum order=data;
  	define '2018/19'n / analysis sum order=data;
  	define '2019/20'n / analysis sum order=data;
	define group1 / order noprint order=data;
	break after group1 / summarize;
		compute after group1;
  			c2 = catx(' - ',group1,'Total');

			endcomp;

quit;

ods excel close;
ods listing;

[Cynthia_sas]

Hi: My suggestion is that you review pages 17 and 28 of this paper https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf  on BREAK processing with the COMPUTE block in PROC REPORT to get multiple summary lines, as shown below:

Hope this helps,

Cynthia

[twildone]

Hi Cynthia,

Thanks for your suggestion.....can you verify whether the link you had provided is correct because I cannot open it.

Thanks

[Cynthia_sas]

Try again, somehow some stray characters got appended to the end of the URL
https://support.sas.com/resources/papers/proceedings17/SAS0431-2017.pdf

if that doesn't work, just go to Google and type this search string:
https://www.google.com/search?&q=zender+SAS431-2017 and the paper should be the first hit on the page that comes back.
Cynthia