Help using Base SAS procedures

Rounding a number before calculating percentages in Proc Tabulate

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

Rounding a number before calculating percentages in Proc Tabulate

I am using proc tabulate to create summary tables. One of the variables I am summing is not a whole number (ex: 0.25). Let's call it "X". I am summing up "X" into different groups (rows), then dividing by the total of "X" across all rows to get a percent   - using reppctsum. In the table, all the calculations are being done properly.

 

However! The client thinks of the sum of "X" as a whole number, even though it comes from "bits and pieces" of all of the underlying X values. For example, imagine you are predicting the likelihood of someone coming into your store, and each person has a probability of coming in, the total number of people predicted to come to the store would be the sum of all of those probabilities -  but if that sum was 20.5 the client (and I) would predict that 20 people would arrive (not 20 people, and half of another person!)

 

So.... I want proc tabulate to show percentages based on the rounded values (not formatted, but actually rounded to the nearest whole number). In the table output I have formatted the "X" values to be whole numbers, so they look correct, but the underlying percentage that is calculated is usually just a little bit off because it is not using a rounded value, but the actual value.

 

Note: I can't round to whole numbers before I do the summation because it is the sum of  "bits and pieces" of each person that gives me the total prediction I need.

 

Is there any way to force the percentage that is calculated (in this case using reppctsum) to be based on a rounded sum?

 

Here is an example of the values I have for each group, what percentage I get, and what I would like.

 

 

Group A:       12 (actually 11.97, formatted as 12)      Percent shown: 5.3% (11.97/225.42)  Percent desired: 12/225 = 5.3%

Group B:       20 (actually 20.42, formatted as 20)      Percent shown: 9.1% (20.42/225.42)  Percent desired: 20/225 = 8.9%

Group C:       35 (actually 35.35, formatted as 35)      Percent shown: 15.7% (35.35/225.42) Percent desired: 35/225 = 15.6%

Group D:       41 (actually 40.77, formatted as 41)     Percent shown: 18.1% (40.77/225.42) Percent desired: 41/225 = 18.2%

Group E:       61 (actually 60.91, formatted as 61)     Percent shown: 27.0% (60.91/225.42) Percent desired: 61/225 = 27.1%

Group F:       56

 

Total:           225 (actually 225.42 formatted to the whole number, 225)

 

The table statement I am using to get these values includes:

 

            pcomex='% of Total'*f=5.*reppctsum

 

where pcomex is the probability of a person "coming to the store"

 

THANKS for any help that you can provide! Smiley Happy


Accepted Solutions
Solution
‎01-18-2017 03:13 PM
Occasional Contributor
Posts: 13

Re: Rounding a number before calculating percentages in Proc Tabulate

Thanks, I was looking for a way to do it right within PROC TABULATE but this will work. I appreciate the help.

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Rounding a number before calculating percentages in Proc Tabulate

Before proc tabulate do the sum with Proc means or summary using your group variable(s) as class variables.

Then round in a data step.

Then display with proc tabulate.

Solution
‎01-18-2017 03:13 PM
Occasional Contributor
Posts: 13

Re: Rounding a number before calculating percentages in Proc Tabulate

Thanks, I was looking for a way to do it right within PROC TABULATE but this will work. I appreciate the help.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 201 views
  • 2 likes
  • 2 in conversation