Using extra break lines to create grand totals and subtotals

[Laurie]

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

[RW9]

Hi,

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

[Laurie]

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?

[RW9]

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.

[Laurie]

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!

[Cynthia_sas]

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;