Proc Report

[Eugenio211]

Hello, I wanted to add sub totals and Total to my proc report steps. I am showing this in an excel format.  below is the code that I have

ods excel file="directory path\Test_List;

proc report data=Test_List;
columns dm location campaign_month, (customer_count converted Conversion_Rate);
define dm/group;
define location/group;
define campaign_month/across order=data;
define customer_count/sum 'Preapprovals';
define converted/sum 'Conversion';

run;

ods excel close;

Current Output:

		Jan 2021			Feb 2021			Mar 2021			Apr 2021			Jun 2021
DM	LOCATION	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate
DM2	1	533	31	6%	22	8	36%	503	16	3%	30	6	20%	485	6	1%
	2	352	18	5%	19	3	16%	330	15	5%	29	7	24%	337	2	1%
	3	576	18	3%	27	2	7%	529	13	2%	36	1	3%	527	3	1%
	4	430	24	6%	12	2	17%	400	18	5%	18	4	22%	372	5	1%
	5	666	32	5%	24	5	21%	612	27	4%	27	3	11%	601	5	1%
	6	473	26	5%	11	3	27%	437	18	4%	26	5	19%	436	8	2%
	7	216	13	6%	10	7	70%	191	12	6%	8	5	63%	166	3	2%
	8	377	25	7%	16	4	25%	343	15	4%	23	3	13%	346	1	0%
	9	352	25	7%	6	2	33%	307	8	3%	23	2	9%	316	5	2%
DM3	10	349	13	4%	8	3	38%	332	15	5%	9	1	11%	304	1	0%
	11	497	17	3%	14	3	21%	477	17	4%	22	2	9%	431	4	1%
	12	782	30	4%	32	6	19%	729	19	3%	51	9	18%	693	6	1%
	13	605	24	4%	19	3	16%	560	12	2%	38	3	8%	517	3	1%
	14	719	55	8%	33	5	15%	626	28	4%	47	9	19%	608	9	1%
	15	347	14	4%	8	3	38%	331	15	5%	16	4	25%	327	0	0%
	16	336	19	6%	19	3	16%	309	7	2%	34	1	3%	332	2	1%
DM4	17	204	10	5%	7	3	43%	181	5	3%	8	1	13%	169	2	1%
	18	136	6	4%	5	2	40%	132	5	4%	5	2	40%	107	0	0%
	19	110	3	3%	2	1	50%	104	3	3%	5	2	40%	80	1	1%
	20	111	1	1%	3	0	0%	106	3	3%	8	1	13%	91	0	0%
	21	86	1	1%	6	3	50%	83	2	2%	3	1	33%	78	0	0%

Desired Ouput:

		Jan 2021			Feb 2021			Mar 2021			Apr 2021			Jun 2021
DM	LOCATION	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate	Preapprovals	Conversion	Conversion_Rate
DM2	SubTotal
	1	533	31	6%	22	8	36%	503	16	3%	30	6	20%	485	6	1%
	2	352	18	5%	19	3	16%	330	15	5%	29	7	24%	337	2	1%
	3	576	18	3%	27	2	7%	529	13	2%	36	1	3%	527	3	1%
	4	430	24	6%	12	2	17%	400	18	5%	18	4	22%	372	5	1%
	5	666	32	5%	24	5	21%	612	27	4%	27	3	11%	601	5	1%
	6	473	26	5%	11	3	27%	437	18	4%	26	5	19%	436	8	2%
	7	216	13	6%	10	7	70%	191	12	6%	8	5	63%	166	3	2%
	8	377	25	7%	16	4	25%	343	15	4%	23	3	13%	346	1	0%
	9	352	25	7%	6	2	33%	307	8	3%	23	2	9%	316	5	2%
DM3	SubTotal
	10	349	13	4%	8	3	38%	332	15	5%	9	1	11%	304	1	0%
	11	497	17	3%	14	3	21%	477	17	4%	22	2	9%	431	4	1%
	12	782	30	4%	32	6	19%	729	19	3%	51	9	18%	693	6	1%
	13	605	24	4%	19	3	16%	560	12	2%	38	3	8%	517	3	1%
	14	719	55	8%	33	5	15%	626	28	4%	47	9	19%	608	9	1%
	15	347	14	4%	8	3	38%	331	15	5%	16	4	25%	327	0	0%
	16	336	19	6%	19	3	16%	309	7	2%	34	1	3%	332	2	1%
DM4	SubTotal
	17	204	10	5%	7	3	43%	181	5	3%	8	1	13%	169	2	1%
	18	136	6	4%	5	2	40%	132	5	4%	5	2	40%	107	0	0%
	19	110	3	3%	2	1	50%	104	3	3%	5	2	40%	80	1	1%
	20	111	1	1%	3	0	0%	106	3	3%	8	1	13%	91	0	0%
	21	86	1	1%	6	3	50%	83	2	2%	3	1	33%	78	0	0%

Thanks a lot.

[Cynthia_sas · Accepted solution]

Hi:

  PROC REPORT has a BREAK statement and an RBREAK statement. You can have the Break line (or summary line) added AFTER a group or order item or BEFORE a group or order item. Without data, no one can test or modify your code. However in a quick look, it seems to me that you need this statement to start:

BREAK BEFORE DM / SUMMARIZE;

RBREAK BEFORE/ SUMMARIZE;

or

BREAK AFTER DM / SUMMARIZE;

RBREAK AFTER/ SUMMARIZE;

  Although I'm not sure how you want your Conversion Rate percentage treated in the sub-totals or totals. You can find examples of BREAK and RBREAK in previous forum postings and in the PROC REPORT documentation.

Cynthia