01-13-2017 12:36 PM
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!
01-13-2017 12:54 PM
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.
Need further help from the community? Please ask a new question.