topic Re: Proc Tabulate percentage column in ODS and Base Reporting

Proc Tabulate percentage column

Naka — Mon, 26 Sep 2016 19:26:14 GMT

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

Re: Proc Tabulate percentage column

Astounding — Mon, 26 Sep 2016 19:14:08 GMT

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.

Re: Proc Tabulate percentage column

Naka — Mon, 26 Sep 2016 19:18:21 GMT

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

Re: Proc Tabulate percentage column

Astounding — Mon, 26 Sep 2016 19:25:32 GMT

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.

Re: Proc Tabulate percentage column

Naka — Mon, 26 Sep 2016 19:29:57 GMT

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.

Re: Proc Tabulate percentage column

ballardw — Mon, 26 Sep 2016 19:40:55 GMT

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.

Re: Proc Tabulate percentage column

Astounding — Mon, 26 Sep 2016 20:49:50 GMT

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.

Re: Proc Tabulate percentage column

Naka — Mon, 26 Sep 2016 20:53:35 GMT

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.

Re: Proc Tabulate percentage column

ballardw — Mon, 26 Sep 2016 21:40:46 GMT

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;

Re: Proc Tabulate percentage column

Cynthia_sas — Tue, 27 Sep 2016 04:36:50 GMT

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