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;
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;
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;
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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.