BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sgnolek
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
sgnolek
Obsidian | Level 7

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

2 REPLIES 2
ballardw
Super User

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
Obsidian | Level 7

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

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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