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
- /
- ODS and Base Reporting
- /
- Proc Tabulate percentage column

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
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2016 02:25 PM - edited 09-26-2016 03:26 PM

Hi Everyone

I have two columns in my table. One is amount, the other is interest.

I want to calculate a third column which is interest / amount expressed as a percentage. I want to have this column for every row including the All row.

Any ideas how to do that using proc tabulate only?

Account Group Interest Amount Rate

1 100 1000 10%

2 50 1000 5%

3 100 10000 1%

Total 250 12000 2.08%

Regards

Accepted Solutions

Solution

09-27-2016
08:00 AM

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

Posted in reply to Astounding

09-26-2016 05:40 PM

This MAY work depending on what else you may be doing. But generally working with percents in Tabulate from two variables can be problematic.

```
data have;
input Account Interest Amount ;
datalines;
1 100 1000
2 50 1000
3 100 10000
;
run;
proc tabulate data=have;
class account;
var interest amount;
table account all,
interest*sum=''*f=best6. amount*sum=''*f=best6. interest=''*pctsum<amount>='Rate'
;
run;
```

All Replies

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

Posted in reply to Naka

09-26-2016 03:14 PM

You will have to use a DATA step first, to compute a single variable holding amount / interest. PROC TABULATE permits just one analysis variable per cell.

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

Posted in reply to Astounding

09-26-2016 03:18 PM

How do I solve the problem where when I want to create an All row, the percentages are summed up?

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

Posted in reply to Naka

09-26-2016 03:25 PM

If you have just one row of data per row in the report, get the mean instead of the sum.

If you have many rows of data per row in the report, you have to do all the calculations ahead of time. That includes generating an extra row with the overall statistics.

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

Posted in reply to Astounding

09-26-2016 03:29 PM

The mean of the percentages will be meaningless. I've updated my original post with an example.

Unfortunately, there are 3 class variables with multiple values and multiple All rows. There must be a better solution out there than a datastep, which will increase the complexity of the code to an unacceptable degree.

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

Posted in reply to Naka

09-26-2016 03:40 PM

Proc Tabulate is not designed to calculate between columns or rows other than the statistics applied to ALL groupings.

Since "interest" is seldom used in sums or many other statistics I suspect your data is row oriented and that Proc Report would work.

If you insist on Tabulate then you will have to pre-calculated everything and then use tabulate just for displaying values.

Solution

09-27-2016
08:00 AM

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

Posted in reply to Astounding

09-26-2016 05:40 PM

This MAY work depending on what else you may be doing. But generally working with percents in Tabulate from two variables can be problematic.

```
data have;
input Account Interest Amount ;
datalines;
1 100 1000
2 50 1000
3 100 10000
;
run;
proc tabulate data=have;
class account;
var interest amount;
table account all,
interest*sum=''*f=best6. amount*sum=''*f=best6. interest=''*pctsum<amount>='Rate'
;
run;
```

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

Posted in reply to Naka

09-26-2016 04:49 PM

If you would be satisfied with a plain vanilla report (no fancy colors or fonts, but complete control over the format otherwise), you could switch to the old style of customized reporting: a DATA step with FILE and PUT statements.

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

Posted in reply to Astounding

09-26-2016 04:52 PM - edited 09-26-2016 04:53 PM

I don't have the flexibility and am stuck with a proc tabulate and all the design and style features that come with it... It is a massive report with very strict requirements.

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

Posted in reply to Naka

09-27-2016 12:36 AM

Hi:

This sounds like it would be a report done better with PROC REPORT than with PROC TABULATE. PROC REPORT allows you to calculate a new column on the report based on existing columns. PROC TABULATE will not create new columns and you would have to use a DATA step program to do what you want, if the percentage can even be calculated as you envision.

There are quite a few examples of using PROC REPORT posted already in the forum.

cynthia