topic Re: proc tabulate in SAS Programming

proc tabulate

Ronein — Wed, 25 Jul 2018 06:27:30 GMT

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;

Re: proc tabulate

BrunoMueller — Wed, 25 Jul 2018 07:43:45 GMT

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;

Re: proc tabulate

Ronein — Wed, 25 Jul 2018 08:53:42 GMT

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;

Re: proc tabulate

BrunoMueller — Wed, 25 Jul 2018 09:22:22 GMT

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?