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
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;
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.
How do I solve the problem where when I want to create an All row, the percentages are summed up?
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.
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.
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.
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;
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.
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.