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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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