proc tabulate

[Ronein]

Hello

Please see following code.

I need to create one more row in the output with percent of each column's total from grand total.

811/1901=43%

1090/1901=57%

1901/1901=100%

I guess that we cannot do it with proc report.

Can anyone show another way to do it with proc tabulate?

Proc report data=sashelp.class nowd;
   columns age sex,(weight=weightsum) weight=TotalSum;
   define age/ group   ;
   define weight / sum;
   define sex/across 'sex'  order=formatted ;
   define weightsum / sum  format=comma12.  ;
   define TotalSum / sum format=comma12. 'All sex';
rbreak after/ summarize;
run;

[BrunoMueller]

You can use Proc TABULATE for this. It allows to have the statistics on the row dimension, see the example below. 

The code also creates two percent formats so that the numbers are displayed as percentages.

proc format;
  picture pct2fmt (round)
  low - < 0  = "099.99%"(prefix='-' )
  0          = "n/a"
  0 - high   = "099.99%"          
  ;

	picture pct1fmt (round)
	low - < 0  = "099.9%"(prefix='-' )
  0          = "n/a"
  0 - high   = "099.9%"          
  ;
run;


proc tabulate data=sashelp.class ;
  class age sex;
  var weight;
  table
    age*sum all*(sum pctsum*f=pct2fmt.)
    ,
    sex*weight all*weight
    / 
    
    row=float
  ;
  keylabel sum= " " pctsum=" ";
run;

You can also use Proc REPORT, you need to add additional variables so that you can have multiple breaklines. The _BREAK_ variable lets you know in which breakline you are and then fill the cells accordingly. Here is an example:

data class;
  set sashelp.class;
  break1 = 1;
  break2 = 1;
run;

%let doNotPrint = noprint;
/*%let doNotPrint = ;*/

Proc report data=class nowd;
  columns break1 break2 age sex,(weight=weightsum) weight=TotalSum _dummy;
  define break1 / group &doNotPrint;
  define break2 / group &doNotPrint;
  define age / group;
  define weight / sum;
  define sex / across 'sex'  order=formatted;
  define weightsum / sum  format=comma12.;
  define TotalSum / sum format=comma12. 'All sex';
  define _dummy / computed &doNotPrint;

  compute _dummy / char length=32;
    _dummy = catx(":", _break_, _c4_, _c5_, totalSum);
    if lowcase(_break_) = "break1" then do;
      _c4_ = _c4_ / TotalSum;
      _c5_ = _c5_ / TotalSum;
      TotalSum = TotalSum / TotalSum;
      call define("_c4_", "format", "percent9.2");
      call define("_c5_", "format", "percent9.2");
      call define("totalSum", "format", "percent9.2");
    end;

  endcomp;

  break after break1 / summarize;
  break after break2 / summarize;
run;

[Ronein]

It is perfect.

I used this code that you showed.

Currently the extra row with percents is working for all age values .

I want to  calculate the extra row (with percent calculations) that will apply only for ages 12-15.

proc format;                           
   picture mypct (round) low-high='009.99%';   
run;
proc tabulate data=sashelp.class ;
  class age sex;
  var weight;
  table
    age*N all*(N pctsum*f=mypct.)
    ,
    sex*weight all*weight
    / 
    row=float
  ;
  keylabel N= " " pctsum=" ";
run;

[BrunoMueller]

So the table should show all the ages 11-16, but the percent calculation should only by done for ages 12-15, is this correct?

Can you explain in more detail why it should be like this?