topic Re: Trouble getting report totals broken out in ODS and Base Reporting

Trouble getting report totals broken out

Laurie — Wed, 17 Sep 2014 21:49:13 GMT

Hi,

I have a problem getting report grand totals to break out the way I want it. From what I have been reading, I need to create dummy breaks and dummy variables. It seems to me there should be an easier way but I don't know how. Has anyone ever done this? and how did you do it? I also can do a union in the dataset and just display but it seems proc report should be able to do this.

I have the following data:

Market_name	Channel_Summary	total_accts_m1	brkt	brkb
Greater Philadelphia	Touchpoint	2	t	b
New England South	Touchpoint	7	t	b
Western/Central Pennsylvania	Touchpoint	1	t	b
_Incomplete Information	Branch Intranet	1	t	b
_Incomplete Information	Touchpoint	2	t	b

I want the output to look like this:

Market Name	Channel	Accounts
Greater Philadelphia	Touchpoint	2
New England South	Touchpoint	7
Western/Central Pennsylvania	Touchpoint	1
_Incomplete Information	Branch Intranet	1
	Touchpoint	2
GRAND TOTAL	TOUCHPOINT	12
GRAND TOTAL	BRANCH INTRANET	1

and the following code:

ods listing close;

ODS tagsets.excelxp

file = "\\wapprib00001040\Prod\output\excel\TESTRPT.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 market_name channel_summary brkt brkb ("July 2014" total_accts_m1 total_accts_m1=nsal_m1);

define market_name / group width=30 'Market Name' format=$30. style(column)={cellwidth=3.0in};

define channel_summary / group width=30 'Channel' format=$30. style(column)={cellwidth=3.0in};

define total_accts_m1 / analysis 'Accounts' center style(column)={cellwidth=0.9in } ;

define nsal_m1 / n f=comma8. 'Count' ;

define brkt / group noprint;

define brkb / group noprint;

break after brkt / summarize;

break after brkb / summarize;

rbreak after / summarize style(summary) = {font_weight=bold font_size=11pt } ;

compute nsal_m1;

if channel_summary = 'Touchpoint' then tcnt + nsal_m1;

else if channel_summary = 'Branch Intranet' then bcnt + nsal_m1;

endcomp;

compute after brkt;

total_accts_m1.sum = tcnt;

channel_summary = 'Number of Touchpoint';

endcomp;

compute after brkb;

total_accts_m1.sum = bcnt;

channel_summary = 'Number of Branch Intranet';

endcomp;

compute after ;

channel_summary = 'Total All';

endcomp;

run ;

ods tagsets.excelxp close;

ods listing;

I modified my code (the above) to try to do these dummy breaks and variables but it gives the below result which doesnt make sense.

		July 2014
Market Name	Channel	Accounts	Count
Greater Philadelphia	Touchpoint	2	1
Greater Philadelphia	Number of Branc	0	1
Greater Philadelphia	Number of Touch	3	1
New England South	Touchpoint	7	1
New England South	Number of Branc	0	1
New England South	Number of Touch	6	1
Western/Central Pennsylvania	Touchpoint	1	1
Western/Central Pennsylvania	Number of Branc	0	1
Western/Central Pennsylvania	Number of Touch	9	1
_Incomplete Information	Branch Intranet	1	1
_Incomplete Information	Number of Branc	2	1
_Incomplete Information	Number of Touch	9	1
	Touchpoint	2	1
_Incomplete Information	Number of Branc	3	1
_Incomplete Information	Number of Touch	12	1
	Total All	13	5

Can anyone help?

Thanks in advance

Laurie

Re: Trouble getting report totals broken out

Ksharp — Thu, 18 Sep 2014 13:04:28 GMT

data have;
infile cards expandtabs;
input Market_name & $40.     Channel_Summary     & $20. total_accts_m1     ;
cards;
Greater Philadelphia       Touchpoint      2     
New England South       Touchpoint      7     
Western/Central Pennsylvania       Touchpoint      1     
_Incomplete Information        Branch Intranet      1
_Incomplete Information        Touchpoint      2
;
run;
ods listing close;
ods pdf file='x.pdf' style=sasweb;
proc report data=have nowd ;
columns Market_name Channel_Summary total_accts_m1;
define Market_name/order;
define      Channel_Summary/display;
define total_accts_m1/display;
compute total_accts_m1;
if Channel_Summary='Touchpoint' then c_sum+total_accts_m1;
 else if Channel_Summary='Branch Intranet' then b_sum+total_accts_m1;
endcomp;
compute after;
line 'GRAND TOTAL     TOUCHPOINT'  c_sum;
line 'GRAND TOTAL     BRANCH INTRANET'  b_sum ;
endcomp;
run;
ods pdf close;
ods listing;

Re: Trouble getting report totals broken out

Laurie — Thu, 18 Sep 2014 15:28:13 GMT

Thanks Ksharp!!! I tried this and it works.... the only problem is that I have 5 more statistics for each month that I will need to total and need the totals at the bottom of the proper column. The line is one big line in excel and hard to control where the totals are.

Plus I have more months and statistics to show like below. Also other sheets have another breakout that needs a subtotal by channel_sumamry too. Do you know how to do it so that if there are multiple months it would do the total breakout and put it in the correct column? Below this report I put the code (which is arrays) that generate multiple months.

		Jul 2014					Aug-14
Market Name	Channel	total_accts_m1	esign_m1	wetsign_m1	lt_m1	gt_m1	total_accts_m2	esign_m2	wetsign_m2	lt_m2	gt_m1
Greater Philadelphia	Touchpoint	2	0	2	0	2	2	0	2	0	2
New England South	Touchpoint	7	1	6	2	4	7	1	6	2	4
Western/Central Pennsylvania	Touchpoint	1	0	1	0	1	1	0	1	0	1
_Incomplete Information	Branch Intranet	1	1	0	0	0	1	1	0	0	0
	Touchpoint	2	0	2	2	0	2	0	2	2	0
	Grand Total Touchpoint	12	1	11	4	7	12	1	11	4	7
	Grand Total Branch Intranet	1	1	0	0	0	1	1	0	0	0

ods listing close;

ODS tagsets.excelxp

file = "&rootdir.\output\excel\test.xls"

style = Barrettsblue

options (

sheet_label = ''

wraptext='yes'

/*absolute_column_width='20'*/);

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

Output Market Trend

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

ods tagsets.excelxp ;

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

column market_name channel_summary

%do i=1 %to &num_months;

("&&label&i" total_accts_m&i esign_m&i wetsign_m&i lt_m&i gt_m&i)

%end;

define market_name / order width=30 'Market Name' format=$30. style(column)={cellwidth=3.0in};

define channel_summary / display width=30 'Channel' format=$30. style(column)={cellwidth=3.0in};

%do i=1 %to &num_months;

define total_accts_m&i / display center style(column)={cellwidth=0.9in } ;

define esign_m&i / analysis center style(column)={cellwidth=0.9in };

define wetsign_m&i / analysis center style(column)={cellwidth=0.9in };

define lt_m&i / analysis center style(column)={cellwidth=0.9in };

define gt_m&i / analysis center style(column)={cellwidth=1.0in };

%end;

%do i=1 %to &num_months;

compute total_accts_m&i;

if Channel_Summary='Touchpoint' then c_sum&i+total_accts_m&i;

else if Channel_Summary='Branch Intranet' then b_sum&i+total_accts_m&i;

endcomp;

%end;

/* rbreak after / summarize style(summary) = {font_weight=bold font_size=11pt } ;*/

compute after / style(lines)= {font_weight=bold font_size=11pt } ;

%do i=1 %to &num_months;

line @31 'GRAND TOTAL TOUCHPOINT' c_sum&i;

line @31 'GRAND TOTAL BRANCH INTRANET' b_sum&i ;

endcomp;

%end;

run ;

Re: Trouble getting report totals broken out

Ksharp — Fri, 19 Sep 2014 12:14:03 GMT

Oh, That is a bad idea to use macro for so many variables. If I were you ,I would like to use data step to get these statistical variables and append them at bottom of the table , and then just simply proc print it .

BTW, you don't need to make so many compute block , only one is enough. in other words, put the code in the last variable compute block .

Xia Keshan

Re: Trouble getting report totals broken out

RW9 — Fri, 19 Sep 2014 12:32:23 GMT

I would agree there. From my side I have to store a copy of the dataset for separate validation, so the dataset should resemble the output as much as possible. Hence we do all the calculations up front:

data have;
length Market_name Channel_Summary $200 total_accts_m1 8 brkt brkb $1;
infile datalines dlm=",";
input Market_name $ Channel_Summary $ total_accts_m1 brkt $ brkb $;
datalines;
Greater Philadelphia,Touchpoint,2,t,b
New England South,Touchpoint,7,t,b
Western/Central Pennsylvania,Touchpoint,1,t,b
_Incomplete Information,Branch Intranet,1,t,b
_Incomplete Information,Touchpoint,2,t,b
;
run;

proc sql;
create table WANT as
select MARKET_NAME,
          CHANNEL_SUMMARY,
          TOTAL_ACCTS_M1
from    WORK.HAVE
union all
select "" as MARKET_NAME,
          "Grand Total Touchpoint" as CHANNEL_SUMMARY,
          SUM(TOTAL_ACCTS_M1) as TOTAL_ACCTS_M1
from    (select * from WORK.HAVE where CHANNEL_SUMMARY="Touchpoint")
union all
select "" as MARKET_NAME,
          "Grand Total Branch Internet" as CHANNEL_SUMMARY,
          SUM(TOTAL_ACCTS_M1) as TOTAL_ACCTS_M1
from    (select * from WORK.HAVE where CHANNEL_SUMMARY="Branch Intranet");
quit;

ods pdf file="s:\temp\rob\out.pdf";
proc report data=want style=statistical nowd;
columns market_name channel_summary total_accts_m1;
run;
ods pdf close;