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;
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
Hi Cynthia,
Thanks for your suggestion.....can you verify whether the link you had provided is correct because I cannot open it.
Thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.