Solved
New Contributor
Posts: 4

# Calculate Percentage with rows and column calculation

Calculating percentages with the N of a row as the denominator.

What I want to do is create a table using proc tabulate, if possible, where the N of the row is used as the denominator for the cell.

Quarter is a class variable, with categories of Q1, Q2, Q3, Q4 (this is to be used as the row). Flag_1 and Flag_2 are binary flag variables; they have values of either 1 or 0.

Basically, I want the sum of my flag variables (flag_1 and flag_2) within a row (quarter class variable) to be the numerator in a cell, divided by the number of observations for that row, where the quarter is Q1 etc.

The desired output is below (Made up numbers for the sum of observations in a row (30, 40, etc); wanted to emphasize that the number of observations with a different quarter vary):

Accepted Solutions
Solution
‎11-27-2017 02:38 PM
Super User
Posts: 2,512

## Re: Calculate Percentage with rows and column calculation

[ Edited ]

sum of var divided by number of obs is called the mean.

data HAVE;
do QUARTER=1 to 4;
do FL1=0 to 1;
do FL2=0 to 1;
do I=1 to 10;
if ranuni(0)>.5 then output;
end;
end;
end;
end;
run;

proc tabulate data=HAVE;
class QUARTER ;
var FL1 FL2;
table QUARTER, n (FL1 FL2)*(sum mean) ;
run;

N FL1 FL2
Sum Mean Sum Mean
QUARTER 18 11.00 0.61 9.00 0.50
1
2 23 13.00 0.57 14.00 0.61
3 18 9.00 0.50 9.00 0.50
4 21 8.00 0.38 10.00 0.48

All Replies
Solution
‎11-27-2017 02:38 PM
Super User
Posts: 2,512

## Re: Calculate Percentage with rows and column calculation

[ Edited ]

sum of var divided by number of obs is called the mean.

data HAVE;
do QUARTER=1 to 4;
do FL1=0 to 1;
do FL2=0 to 1;
do I=1 to 10;
if ranuni(0)>.5 then output;
end;
end;
end;
end;
run;

proc tabulate data=HAVE;
class QUARTER ;
var FL1 FL2;
table QUARTER, n (FL1 FL2)*(sum mean) ;
run;

N FL1 FL2
Sum Mean Sum Mean
QUARTER 18 11.00 0.61 9.00 0.50
1
2 23 13.00 0.57 14.00 0.61
3 18 9.00 0.50 9.00 0.50
4 21 8.00 0.38 10.00 0.48

New Contributor
Posts: 4

## Re: Calculate Percentage with rows and column calculation

Thank you, I was looking/thinking about it incorrectly.
Super User
Posts: 13,941

## Re: Calculate Percentage with rows and column calculation

renalstats wrote:

Calculating percentages with the N of a row as the denominator.

What I want to do is create a table using proc tabulate, if possible, where the N of the row is used as the denominator for the cell.

Quarter is a class variable, with categories of Q1, Q2, Q3, Q4 (this is to be used as the row). Flag_1 and Flag_2 are binary flag variables; they have values of either 1 or 0.

Basically, I want the sum of my flag variables (flag_1 and flag_2) within a row (quarter class variable) to be the numerator in a cell, divided by the number of observations for that row, where the quarter is Q1 etc.

The desired output is below (Made up numbers for the sum of observations in a row (30, 40, etc); wanted to emphasize that the number of observations with a different quarter vary):

Your data picture shows a sum/sum your description says a sum divided by an n (number of observations). so which is it you want?

Proc tabulate has statistics colpctsum and rowpctsum if the denominatior is a sum.

You much better off providing an example data set in data step form as @ChrisNZ did and then actual numbers in your result so we can check code behavior.

☑ This topic is solved.