BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Naka
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

9 REPLIES 9
Astounding
PROC Star

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.

Naka
Calcite | Level 5

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

Astounding
PROC Star

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.

Naka
Calcite | Level 5

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.

ballardw
Super User

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.

ballardw
Super User

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; 
Astounding
PROC Star

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.

Naka
Calcite | Level 5

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.

Cynthia_sas
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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