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! 🙂
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.