turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Rounding a number before calculating percentages i...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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!

Accepted Solutions

Solution

01-18-2017
03:13 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

01-18-2017 03:13 PM

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sgnolek

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.

Solution

01-18-2017
03:13 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

01-18-2017 03:13 PM