The SAS Output Delivery System and reporting techniques

Proc Tabulate percentage column

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Proc Tabulate percentage column

[ Edited ]

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
Super User
Posts: 11,134

Re: Proc Tabulate percentage column

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; 

View solution in original post


All Replies
Super User
Posts: 5,366

Re: Proc Tabulate percentage column

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.

New Contributor
Posts: 4

Re: Proc Tabulate percentage column

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

Super User
Posts: 5,366

Re: Proc Tabulate percentage column

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.

New Contributor
Posts: 4

Re: Proc Tabulate percentage column

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.

Super User
Posts: 11,134

Re: Proc Tabulate percentage column

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
Super User
Posts: 11,134

Re: Proc Tabulate percentage column

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; 
Super User
Posts: 5,366

Re: Proc Tabulate percentage column

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.

New Contributor
Posts: 4

Re: Proc Tabulate percentage column

[ Edited ]

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.

SAS Super FREQ
Posts: 8,820

Re: Proc Tabulate percentage column

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 661 views
  • 1 like
  • 4 in conversation