PROC Tabulate: percentages.

[steve_citi]

Suppose I have 12 records, one per month. One variable on the record represents the dollar value of all loans defaulted within that month. The other variable on these monthly records, however, is not really "monthly". It represents the total value of all outstanding loans at this point in time.

This works well for a monthly report. To calculate the percentage of defaulted loans, simply divide the value of the defaulted loans by the total value of all outstanding loans found on the monthly record. Since both of these variables are on the same record, it is quite easy.

For a monthly report you would do the following:

proc tabulate data=myinput;
class asofmonth;
var default outstanding;
table default =' '*sum='Default $'
outstanding =' '*sum='Outstanding'
default =' '*pctsum='Default Percent',
asofmonth



If you want to do a quarterly report however, things get a little more complicated. For the numerator you simply sum the value of defaulted loans for the three months. For the denominator you cannot sum the values, but MUST use the average of the three months.

My question is this: How do you tell tabulate to use the average for the denominator? Is that possible?

proc tabulate data=myinput;
class asofquarter;
var default outstanding;
table default =' '*sum='Default $'
outstanding =' '*mean='Outstanding'
default =' '*?????????????='Default Percent',
asofquarter

Mathematically what I would like where the ?????? are is the mean of the outstandings calculated for this quarter.

What is the syntax to accomplish this? Is it even possible?

[Cynthia_sas]

Hi:
PCTSUM has 'SUM' in the name because the formula is:
[pre]
SUM of analysis variable in one cell * 100
--------------divided by----------------------------------
SUM of analysis variable for all cells (grand total)

and in a TABLE statement, it looks like:
table row-dim,
classvar*(sum*numvar pctsum*numvar);
[/pre]

... but it's not the called PCTMEAN statistic. When you use a custom denominator, you are telling TABULATE to use a denomiator based on some other variable on the observation (as an alternative to the grand total of the analysis variable). But you can't get TABULATE to do anything other than SUM the other variable being used for the denominator....not when you are passing it multiple records per quarter.

You might have to "precalculate" your quarter summary and the mean of the 3 months defaulted loans for TABULATE ahead of time. This would mean that that your monthly report and your quarterly report (and probably your yearly report) would not end up using the same basic code model. Or, you could move to PROC REPORT, where it would be possible to both summarize and capture the mean of the defaulted loans for the quarter and to do the division yourself in a COMPUTE block.

cynthia