topic Calculate Percentage with rows and column calculation in SAS Programming

Calculate Percentage with rows and column calculation

renalstats — Mon, 27 Nov 2017 03:30:58 GMT

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):

Re: Calculate Percentage with rows and column calculation

ChrisNZ — Mon, 27 Nov 2017 04:21:50 GMT

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
	N	Sum	Mean	Sum	Mean
QUARTER	18	11.00	0.61	9.00	0.50
1	18	11.00	0.61	9.00	0.50
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

Re: Calculate Percentage with rows and column calculation

ballardw — Mon, 27 Nov 2017 15:59:48 GMT

@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.

Re: Calculate Percentage with rows and column calculation

renalstats — Mon, 27 Nov 2017 19:38:47 GMT

Thank you, I was looking/thinking about it incorrectly.