turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Calculate Percentage with rows and column calculat...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-26-2017 10:30 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to renalstats

11-26-2017 11:20 PM - edited 11-26-2017 11:21 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to renalstats

11-26-2017 11:20 PM - edited 11-26-2017 11:21 PM

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 |

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ChrisNZ

11-27-2017 02:38 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to renalstats

11-27-2017 10:59 AM

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.