Hi:
I assume you want something like THIS:
Where the number in each cell is the percent of the ROW total, not the percent of the Grand Total. PROC TABULATE has special percent keywords for this (ROWPCTN and ROWPCTSUM, along with COLPCTN and COLPCTSUM) but PROC REPORT does not have those keywords available.
Instead, if you want to calculate a ROW percent (so that each row adds up to 100% than you need to calculate it yourself. I used an ACROSS item to show a separate column for F vs M.
The COMPUTE block in this case, is more complex, since I could not use the simple PCTN for the calculation. But first, I had to make some fake data since I only wanted to show 2 rows plus the total row on the report:
data fakedata;
length grpvar $10;
set sashelp.class;
if age le 13 then grpvar = 'Group 1';
else grpvar = 'Group 2';
run;
Then I had to create some calculated variables to hold the totals, but, due to the use of ACROSS items, the variables needed to be referred to as their absolute column numbers:
So when you see references to _c3_, _c4_ and _c5_, etc, those are needed any time you use an ACROSS item and you need to calculate a value that is also nested under an ACROSS item. In this case, the SEX variable for F and M was defined as an ACROSS item. That means the STOT value and the PCT value for F needs to be distinguished from the STOT and the PCT value for M in order to calculate a separate CALCCOL value for each unique group.
Here's the PROC REPORT code to generate the above report and the report that I initially showed:
proc report data=fakedata;
title '3a) Only showing the calculated column and total';
column ('Group' grpvar) n=rtot sex,(n=stot calcpct calccol) ('Total' totcol);
define grpvar / group 'Grpvar';
define rtot / 'rtot' /* noprint */;
define sex / across "With computed % and Row %";
define stot / 'stot' /* noprint */;
define calcpct / computed 'Pct' f=percent7.1 /* noprint */;
define calccol / computed 'CALCCOL'
style(column)={just=r};
define totcol / computed 'TOTCOL'
style(column)={just=r};
compute calcpct;
_c4_ = divide(_c3_ , rtot);
_c7_ = divide(_c6_ , rtot);
endcomp;
compute calccol / character length = 15;
if _c3_ gt . then
_c5_ = put(_c3_,2.0)||' ('||right(put(_c4_,percent7.1))||')';
if _c6_ gt . then
_c8_ = put(_c6_,2.0)||' ('||right(put(_c7_,percent7.1))||')';
endcomp;
compute totcol / character length = 15;
tmp = divide(rtot, rtot);
totcol = put(rtot,2.0)||' ('||right(put(tmp,percent7.1))||')';
endcomp;
rbreak after / summarize;
compute after;
grpvar = 'Total';
endcomp;
run;
proc report data=fakedata;
title '3b) Only showing the calculated column and total';
column ('Group' grpvar) n=rtot sex,(n=stot calcpct calccol) ('Total' totcol);
define grpvar / group ' ';
define rtot / 'rtot' noprint ;
define sex / across "With computed % and Row %";
define stot / 'stot' noprint ;
define calcpct / computed 'Pct' f=percent7.1 noprint ;
define calccol / computed 'CALCCOL'
style(column)={just=r};
define totcol / computed ' '
style(column)={just=r};
compute calcpct;
_c4_ = divide(_c3_ , rtot);
_c7_ = divide(_c6_ , rtot);
endcomp;
compute calccol / character length = 15;
if _c3_ gt . then
_c5_ = put(_c3_,2.0)||' ('||right(put(_c4_,percent7.1))||')';
if _c6_ gt . then
_c8_ = put(_c6_,2.0)||' ('||right(put(_c7_,percent7.1))||')';
endcomp;
compute totcol / character length = 15;
tmp = divide(rtot, rtot);
totcol = put(rtot,2.0)||' ('||right(put(tmp,percent7.1))||')';
endcomp;
rbreak after / summarize;
compute after;
grpvar = 'Total';
endcomp;
run;
Hope this helps,
Cynthia
... View more