DATA Step, Macro, Functions and more

Calculate Percentage with rows and column calculation

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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

_UAG24T3.png

 

 


Accepted Solutions
Solution
‎11-27-2017 02:38 PM
PROC Star
Posts: 2,231

Re: Calculate Percentage with rows and column calculation

[ Edited ]
Posted in reply to renalstats

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

 

 

 

View solution in original post


All Replies
Solution
‎11-27-2017 02:38 PM
PROC Star
Posts: 2,231

Re: Calculate Percentage with rows and column calculation

[ Edited ]
Posted in reply to renalstats

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,084

Re: Calculate Percentage with rows and column calculation

Posted in reply to renalstats

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

_UAG24T3.png

 

 


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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 190 views
  • 2 likes
  • 3 in conversation