The SAS Output Delivery System and reporting techniques

Trouble getting report totals broken out

Reply
Occasional Contributor
Posts: 5

Trouble getting report totals broken out

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_nameChannel_Summarytotal_accts_m1brktbrkb
Greater PhiladelphiaTouchpoint2tb
New England SouthTouchpoint7tb
Western/Central PennsylvaniaTouchpoint1tb
_Incomplete InformationBranch Intranet1tb
_Incomplete InformationTouchpoint2tb

I want the output to look like this:

Market NameChannelAccounts
Greater PhiladelphiaTouchpoint2
New England SouthTouchpoint7
Western/Central PennsylvaniaTouchpoint1
_Incomplete InformationBranch Intranet1
Touchpoint2
GRAND TOTAL TOUCHPOINT12
GRAND TOTAL BRANCH INTRANET1

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 NameChannelAccountsCount
Greater PhiladelphiaTouchpoint21
Greater PhiladelphiaNumber of Branc01
Greater PhiladelphiaNumber of Touch31
New England SouthTouchpoint71
New England SouthNumber of Branc01
New England SouthNumber of Touch61
Western/Central PennsylvaniaTouchpoint11
Western/Central PennsylvaniaNumber of Branc01
Western/Central PennsylvaniaNumber of Touch91
_Incomplete InformationBranch Intranet11
_Incomplete InformationNumber of Branc21
_Incomplete InformationNumber of Touch91
Touchpoint21
_Incomplete InformationNumber of Branc31
_Incomplete InformationNumber of Touch121
Total All135

Can anyone help?

Thanks in advance

Laurie

Grand Advisor
Posts: 9,584

Re: Trouble getting report totals broken out

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;

Occasional Contributor
Posts: 5

Re: Trouble getting report totals broken out

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 2014Aug-14
Market NameChanneltotal_accts_m1esign_m1wetsign_m1lt_m1gt_m1total_accts_m2esign_m2wetsign_m2lt_m2gt_m1
Greater PhiladelphiaTouchpoint2020220202
New England SouthTouchpoint7162471624
Western/Central PennsylvaniaTouchpoint1010110101
_Incomplete InformationBranch Intranet1100011000
Touchpoint2022020220
Grand Total Touchpoint12111471211147
Grand Total Branch Intranet1100011000

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 ;


Grand Advisor
Posts: 9,584

Re: Trouble getting report totals broken out

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Trouble getting report totals broken out

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;

Ask a Question
Discussion stats
  • 4 replies
  • 514 views
  • 0 likes
  • 3 in conversation