Proc Report -splits of subtotals

[melfb]

I am using the following code:


proc report data=work.authsummary_h
nowd colwidth=10 spacing=5 headskip headline split='*' ;
column monthnum monthname trans_outcome2 card_type, frequency ,(sum pctsum) grand_tot ;

define monthnum / group order=data noprint ;
define monthname / group 'Month' order=data ;
define trans_outcome2 / group ' ' ;
define card_type / across 'Cardtype' ;
define frequency / analysis sum ' ' format=comma12. ;
define grand_tot / computed 'Grand Total' format=comma12. ;
define sum / ' ' format=comma15. ;
define pctsum / ' ' format=percent6.1 ;

break after monthname / summarize skip suppress;

compute grand_tot ;
grand_tot=_c5_+ _c6_+ _c7_+ _c8_+ _c9_ ;
endcomp ;

rbreak after /summarize ;

run ;



to generate an output similar to this:

type1 type2 GrandTotal
Mar approved 76,200 31% 5,391,685 34% 6,728,391 33%
declined 5,245 2.1% 116,789 0.7% 185,366 0.9%

81,445 33% 5,508,474 34% 6,913,757 34%

Apr approved 74,359 30% 4,949,625 31% 6,188,047 31%
declined 4,655 1.9% 104,994 0.7% 169,216 0.8%
79,014 32% 5,054,619 31% 6,357,263 31%

May approved 82,624 33% 5,392,424 34% 6,758,968 33%
declined 5,386 2.2% 111,731 0.7% 180,305 0.8%
88,010 35% 5,504,155 34% 6,939,273 34%

248,469 100% 16,067,248 100% 20,210,293 100%

However, for each month, I would like the % split to show as a % of the month
subtotal. For example, for March:

corp delta delta Grand
Mar approved 76,200 94% 5,391,685 98% 6,728,391 97%
declined 5,245 6% 116,789 2% 185,366 3%
81,445 100% 5,508,474 100% 6,913,757 100%

Then for the grand total at the bottom of the report, I would only want to show
the total figs (ie no percentages)

Is this possible using proc report?

 

[ChrisW75]

I've done some of this sort of thing with Proc report and it's really tricky to do. What you need to do is a COMPUTE step within the report to fiddle the numbers.

I would look into Proc Tabulate instead. It is also a little fiddly, but it does this stuff natively so it's pretty straightforwards once you get to grips with it.

Looking back through my code, I couldn't find any examples of me doing this with Proc Report, though I know I've tried. I think I mayhave given up and recorded it in PRoc Tabulate instead.

Here's an example I found using Proc Tabulate, it might be a good starting point for you.

proc tabulate data=paymts format=comma15.2;

  class Supplier payee_type_cd;
  var scheme_payment_am;

  format payee_type_cd $payee.;

  keylabel n = 'No'
           sum = '$'
           rowpctn = '% of Supplier Count'
           all = ' '
           ;
  label payee_type_cd = 'Payee Type'
        scheme_payment_am = ' '
         ;

  table Supplier='' all='Total',
        payee_type_cd*scheme_payment_am=' '*
          (n*f=comma8.0 rowpctn) all='Supplier Totals' scheme_payment_am=''*colpctn='Overall %age'
           / nocontinued  box='Supplier'
	   ;

  title 'Distribution of Payments';
	   
run;

Note - The "nocontinued" option is a fix for a problem in ODS where it was putting "continued" at the top of the page, even though it wasn't wrapped over a page. This may not be applicable to you.

 

[melfb]

Thanks Chris, I'll try proc tabulate.