topic Re: Using extra break lines to create grand totals and subtotals in ODS and Base Reporting

Using extra break lines to create grand totals and subtotals

Laurie — Tue, 23 Sep 2014 15:21:44 GMT

Hi,

I have a problem creating subtotals. I am outputting using proc report to excel. So I want the totals in the appropriate column (don't want to use the line command). I have the following code and output. I also listed at the very bottom what I want as the ultimate output. I am at a loss how to use another set of "fake" breaks to create a subtotal by market. Can anyone help?

Data;

t b Greater Philadelphia Touchpoint 2

t b New England South Touchpoint 7

t b New York Branch Intranet 0

t b New York Touchpoint 0

t b Ohio Touchpoint 0

t b Western/Central Pennsylvania Touchpoint 1

t b _Incomplete Information Branch Intranet 1

t b _Incomplete Information Touchpoint 2

Code:

ods listing close;

ODS tagsets.excelxp

file = "\\wapprib00001040\Prod\output\excel\TESTRPT_sub.xls"

style = Barrettsblue

options (

sheet_label = ''

wraptext='yes'

/*absolute_column_width='20'*/);

/**************************************************************

Output Market Trend

****************************************************************/

ods tagsets.excelxp options(sheet_name='Market Trend' );

proc report data = market_trend_tmp1 headskip split = '*' missing wrap ;

column brkt brkb market_name channel_summary

total_accts_m1 total_accts_m1=SumAccounts1 ;

define market_name / group 'Market Name' ;

define channel_summary / group 'Channel';

define brkt / group ;

define brkb / group ;

define total_accts_m1 / sum 'Accounts' ;

define SumAccounts1 / sum ;

/*break after market_name / summarize;*/

break after brkt / summarize;

break after brkb / summarize;

compute SumAccounts1;

if channel_summary = 'Touchpoint' then TPTSumAccounts1 + SumAccounts1;

else if channel_summary = 'Branch Intranet' then BRISumAccounts1 + SumAccounts1;

endcomp;

compute after brkt;

total_accts_m1.sum = TPTSumAccounts1;

channel_summary = 'Number of Touchpoint';

endcomp;

compute after brkb;

total_accts_m1.sum = BRISumAccounts1;

channel_summary = 'Number of Branch Intranet';

endcomp;

run ;

ods tagsets.excelxp close;

ods listing;

Output Have from above:

brkt	brkb	Market Name	Channel	Accounts	total_accts_m1
t	b	Greater Philadelphia	Touchpoint	2	2
		New England South	Touchpoint	7	7
		New York	Branch Intranet	0	0
			Touchpoint	0	0
		Ohio	Touchpoint	0	0
		Western/Central Pennsylvania	Touchpoint	1	1
		_Incomplete Information	Branch Intranet	1	1
			Touchpoint	2	2
t	b		Number of Branc	1	13
t			Number of Touch	12	13

I want the below output:

brkt	brkb	Market Name	Channel	Accounts	total_accts_m1
t	b	Greater Philadelphia	Touchpoint	2	2
		Need subtotals by Market (broken out) --------->	Subtotal of Branc	0
		Need subtotals by Market (broken out) --------->	Subtotal of Touch	2
		New England South	Touchpoint	7	7
		Need subtotals by Market (broken out) --------->	Subtotal of Branc	0
		Need subtotals by Market (broken out) --------->	Subtotal of Touch	7
		New York	Branch Intranet	0	0
			Touchpoint	0	0
		Need subtotals by Market (broken out) --------->	Subtotal of Branc	0
		Need subtotals by Market (broken out) --------->	Subtotal of Touch	0
		Ohio	Touchpoint	0	0
		Need subtotals by Market (broken out) --------->	Subtotal of Branc	0
		Need subtotals by Market (broken out) --------->	Subtotal of Touch	0
		Western/Central Pennsylvania	Touchpoint	1	1
		Need subtotals by Market (broken out) --------->	Subtotal of Branc	0
		Need subtotals by Market (broken out) --------->	Subtotal of Touch	1
		_Incomplete Information	Branch Intranet	1	1
			Touchpoint	2	2
		Need subtotals by Market (broken out) --------->	Subtotal of Branc	0
		Need subtotals by Market (broken out) --------->	Subtotal of Touch	1
t	b	I have this working	Grand Total of Branc	1	13
t		I have this working	Grand Total of Touch	12	13

Re: Using extra break lines to create grand totals and subtotals

RW9 — Tue, 23 Sep 2014 15:25:12 GMT

Hi,

Did we not cover this in: https://communities.sas.com/thread/61301?

Re: Using extra break lines to create grand totals and subtotals

Laurie — Tue, 23 Sep 2014 15:40:03 GMT

Yes thanks for your reply! I have too many additional statistics to do a union. I did get it to work as stated above for the grand totals without doing a union. But now need subtotals as well.

This is only a dumbed down example of the actual report code. I have 12 months of data and each month has 5 statistics to sum up. Also I am using macros to output it since the number of months to display is dynamic (rolling 12 months and accumulating months from report inception i.e. today I might have 1 month of data to display and 3 months from now I have 3 months to display). It would be easier in a proc report to do the calculations if possible.

Maybe there is a way to add more dummy break lines to get the subtotals by market to work?

Re: Using extra break lines to create grand totals and subtotals

RW9 — Tue, 23 Sep 2014 18:16:14 GMT

You do not need to union, do your sums with a by group:

proc sql;

insert into DATA

select MONTH,

SUM(STAT1) as STAT1,

SUM(STAT2) as STAT2

...

group by MONTH;

run;

There are many ways to get totals/statistics and put them into datasets. With the by grouping you also don't need to know the actual groups up front.

Re: Using extra break lines to create grand totals and subtotals

Laurie — Tue, 23 Sep 2014 18:39:55 GMT

Thanks again for your answer. I have way too many variables to do the calcs in sql. I am still searching for another way to get subtotals. I have the grand totals working so I was hoping for some help on the subtotals. If there is a way in proc report I would welcome any thoughts. Thanks again I appreciate the reply.

Thanks!

Re: Using extra break lines to create grand totals and subtotals

Cynthia_sas — Tue, 23 Sep 2014 23:41:25 GMT

Hi:

You can make extra break lines with PROC REPORT. But you will need a "helper" variable for every break line. This paper http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf has an example starting on page 7. I have some sample code that uses SASHELP.CLASS, so it should be easy for you to run and see the difference between program #1, #2 and #3 (where all the "helper" variables are hidden. It should be easy to "macro-ize" code like this because what you add in your "extra" compute block for your helper variables should fall into some pattern that lends itself to generation with a macro program.

Cynthia

** make some fake data with SASHELP.CLASS;

** all helper variables are called "xline";

data helper;

length sex name $25;

set sashelp.class;

xline1 = 'F_mean';

xline2 = 'M_mean';

xline3 = 'height';

xline4 = 'weight';

** make a few "sum" variables which will be 0 or 1 based on condition;

if height > 66 then tall=1;

else tall = 0;

if weight > 125 then heavy = 1;

else heavy = 0;

run;

ods listing close;

options missing = ' ';

ods html file='c:\temp\use_helper_var.html';

proc report data=helper nowd;

title '1) show all helper variables';

title2 'notice how all extra summary lines show same statistics';

column xline1 xline2 xline3 xline4 tall heavy sex name age height weight;

define xline1 / order;

define xline2 / order;

define xline3 / order;

define xline4 / order;

define tall / sum;

define heavy / sum;

define sex / order;

define name / order;

define age / display;

define height / mean f=8.2;

define weight / mean f=8.2;

break after xline1 / summarize;

break after xline2 / summarize;

break after xline3 / summarize;

break after xline4 / summarize;

rbreak after / summarize;

run;

proc report data=helper nowd;

title '2) reassign totals from helper variables tall and heavy';

title2 'and grab female and male averages to use on extra break lines';

column xline1 xline2 xline3 xline4 tall heavy sex name age height weight;

define xline1 / order;

define xline2 / order;

define xline3 / order;

define xline4 / order;

define tall / sum;

define heavy / sum;

define sex / order;

define name / order;

define age / display;

define height / mean f=8.2;

define weight / mean f=8.2;

compute before sex;

** grab and hold stats for each gender in temp variables;

if sex = 'F' then do;

ht_F_mean = round(height.mean,.01);

wt_F_mean = round(weight.mean,.01);

end;

else if sex = 'M' then do;

ht_M_mean = round(height.mean,.01);

wt_M_mean = round(weight.mean,.01);

end;

endcomp;

break after xline1 / summarize;

break after xline2 / summarize;

break after xline3 / summarize;

break after xline4 / summarize;

rbreak after / summarize;

compute after xline1;

sex = 'Female Averages';

height.mean = ht_F_mean;

weight.mean = wt_F_mean;

endcomp;

compute after xline2;

sex = 'Male Averages';

height.mean = ht_M_mean;

weight.mean = wt_M_mean;

endcomp;

compute after xline3;

sex = 'Students over 66" tall';

height.mean = tall.sum;

call define('height.mean','format','8.0');

weight.mean = .;

endcomp;

compute after xline4;

sex = 'Students over 125 lbs';

weight.mean = heavy.sum;

call define('weight.mean','format','8.0');

height.mean = .;

endcomp;

compute after;

sex = 'Overall Averages';

endcomp;

run;

proc report data=helper nowd;

title '3) Using NOPRINT with same code';

column xline1 xline2 xline3 xline4 tall heavy sex name age height weight;

define xline1 / order noprint;

define xline2 / order noprint;

define xline3 / order noprint;

define xline4 / order noprint;

define tall / sum noprint;

define heavy / sum noprint;

define sex / order;

define name / order;

define age / display;

define height / mean f=8.2;

define weight / mean f=8.2;

compute before sex;

** grab and hold stats for each gender in temp variables;

if sex = 'F' then do;

ht_F_mean = round(height.mean,.01);

wt_F_mean = round(weight.mean,.01);

end;

else if sex = 'M' then do;

ht_M_mean = round(height.mean,.01);

wt_M_mean = round(weight.mean,.01);

end;

endcomp;

break after xline1 / summarize;

break after xline2 / summarize;

break after xline3 / summarize;

break after xline4 / summarize;

rbreak after / summarize;

compute after xline1;

sex = 'Female Averages';

height.mean = ht_F_mean;

weight.mean = wt_F_mean;

endcomp;

compute after xline2;

sex = 'Male Averages';

height.mean = ht_M_mean;

weight.mean = wt_M_mean;

endcomp;

compute after xline3;

sex = 'Students over 66" tall';

height.mean = tall.sum;

call define('height.mean','format','8.0');

weight.mean = .;

endcomp;

compute after xline4;

sex = 'Students over 125 lbs';

weight.mean = heavy.sum;

call define('weight.mean','format','8.0');

height.mean = .;

endcomp;

compute after;

sex = 'Overall Averages';

endcomp;

run;

ods html close;