Rounding a number before calculating percentages in Proc Tabulate

[sgnolek]

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 😧       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! 🙂

[ballardw]

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.

[sgnolek · Accepted solution]

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