topic proc tabulate pctsum by group? in SAS Procedures

proc tabulate pctsum by group?

FrankE — Wed, 13 Apr 2011 15:37:07 GMT

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!!

Re: proc tabulate pctsum by group?

Cynthia_sas — Wed, 13 Apr 2011 16:19:03 GMT

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

Re: proc tabulate pctsum by group?

FrankE — Wed, 13 Apr 2011 17:24:33 GMT

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