CODEWITHSUNDEEP
sas
Karim L
Karim L

Reputation: 90

Proc tabulate - custom statistic

Suppose I have the following dataset :

Category  Premium1  Premium2
A         10        20
A         15        40
B         10        15
C         20        25

Using proc tabulate (this is a simplified exemple, I have more classification variables and two-way tables), I want to display for each category the % change of Premium2 over Premium1.

Right now I have created a Premium2_over_Premium1 variable, and I display a weighted average of this variable, where the weight is Premium1. But that gives me a factor, not a change in percentage. For example for category A I will get ((20/10)*10 + (40/15)*15)/(10 + 15)=2.4, but what I would like to see is 2.4 - 1 = 1.4 = 140%.

It seems like a simple enough task but I couldn't find a way to do it! Any help much appreciated.

Upvotes: 2

Views: 272

Answers (1)

Joe
Joe

Reputation: 63424

You can do this in PROC REPORT, but probably not in PROC TABULATE, at least not without an intervening datastep.

I don't completely follow what you're wanting from the percent change variable, as what you're describing doesn't match what I think of percent change meaning, but I think this gets you the result you asked for. If not, the concept should be easy enough to figure out.

data have;
input Category $ Premium1  Premium2;
datalines;
A         10        20
A         15        40
B         10        15
C         20        25
;;;;
run;

proc report data=have nowd;
columns category premium1 premium2 pctChg;
define category/group;           *like CLASS in most other procs;
define premium1/analysis mean;   *use for analysis, show the mean;
define premium2/analysis mean;   *same, could include NOPRINT to hide these columns;
define pctChg/computed format=percent9.1;  *calculated (computed) column, with a format;
compute pctChg;                  *compute (calculate) the variable;
  pctChg = premium2.mean/premium1.mean - 1;  *to refer to analysis vars, use <var>.<statistic>;
endcomp;
run;

Upvotes: 1

Related Questions