proc tabulate pctsum by group?

[FrankE]

Hi guys,

So sorry as this is probably really simple but I've searched and read papers and still cant quite get this. Basically, I've got several groups and subgroups each with an ep_amt and a loss_amt. What I want to do is divide loss_amt by ep_amt for each group. I know its pctsum that I need but I can't get it just right. Here's my code:

data test_data;
input layer $10.
asset_range $10.
reinsurer_group $10.
ep_amt 10.2
loss_amt 10.2
;
cards;
layer1 range1 reins1 1000.21 100.50
layer1 range1 reins2 300.00 200.75
layer1 range2 reins1 3000.00 201.25
layer2 range1 reins1 3001.32 222.23
;

run;
proc tabulate data=test_data f=10.2;
var ep_amt loss_amt;
class layer asset_range reinsurer_group;
table layer * asset_range,
reinsurer_group * (ep_amt loss_amt) * sum;
run;


That gets me almost there. What I now need is to insert a column under each re-insurer that is the loss_amt / ep_amt. Any help would be appreciated!

Thanks!!

[Cynthia_sas]

Hi:
TABULATE supports the use of custom denominator definitions through the use of the < and > operators, as shown in the code below. Since SUM will be the default statistic for EP_AMT and LOSS_AMT, you do not need SUM in the TABLE statement for those 2 variables and it simplifies the TABLE statement. PCTSUM allows you to specify EP_AMT as the denominator.
[pre]
proc tabulate data=test_data f=10.2;
var ep_amt loss_amt;
class layer asset_range reinsurer_group;
table layer * asset_range,
reinsurer_group * (ep_amt loss_amt loss_amt*pctsum<ep_amt>)
/ box='Custom Denom';
run;
[/pre]

cynthia

[FrankE]

You are amazing as usual! Thanks for your help (again) 🙂