New Contributor
Posts: 4

# Rolling 12 months, calculating frequence

Hi,

I'm looking for an easy way to calculate an avarage frequency of reported claims for the last 4 (to keep it easy) months. So for every observation (month) I will have to create a new variable that is the ratio between de sum of the claims of the last 4 months, over the sum of contracts in 'portefeuille' of the last 4 months.

data out;

input jaar maand claims portefeuille;

cards;

2014 01 575 250555

2014 02 555 251555

2014 03 501 252256

2014 04 556 252222

2014 05 587 253000

2014 06 595 253599

2014 07 459 254568

2014 08 531 254998

2014 09 598 255998

2014 10 358 256687

2014 11 589 257169

2014 12 456 258859

2015 01 598 259589

2015 02 433 264210

;

run;

This is what I do;

data out1;

set out;

cl1=lag(claims);

cl2=lag2(claims);

cl3=lag3(claims);

ptf1=lag(ptf);

ptf1=lag1(ptf);

ptf3=lag2(ptf);

freq=(claims+cl1+cl2+cl3)/(ptf+ptf1+ptf2+ptf3);

run;

For this data set and 4 months, this works fine. But when I want to do this for more months, variables , ... this procedure is quite extencive. Is there an other way? I have been looking for a 'Do While' and 'Array', but I don't find anything that works for me.

Leonard

Posts: 5,521

## Re: Rolling 12 months, calculating frequence

[ Edited ]

Using an array:

``````data have;
input jaar maand claims ptf;
cards;
2014 01 575 250555
2014 02 555 251555
2014 03 501 252256
2014 04 556 252222
2014 05 587 253000
2014 06 595 253599
2014 07 459 254568
2014 08 531 254998
2014 09 598 255998
2014 10 358 256687
2014 11 589 257169
2014 12 456 258859
2015 01 598 259589
2015 02 433 264210
;

%let nbMonths=4;

data want;
array sc(0:&nbMonths) _temporary_;
array sp(0:&nbMonths) _temporary_;
set have;
sc{mod(_n_, &nbMonths)} = claims;
sp{mod(_n_, &nbMonths)} = ptf;
if n(of sc{*}) = &nbMonths and n(of sp{*}) = &nbMonths then claimRate = sum(of sc{*}) / sum(of sp{*});
run;

proc print data=want noobs; run;``````

PG
Regular Contributor
Posts: 161

## Re: Rolling 12 months, calculating frequence

[ Edited ]

Hello,

Here is another way with PROC SQL....

``````data in;
input y:4. m:2. claims:3. ptf:8.;
cards;
2014 01 575 250555
2014 02 555 251555
2014 03 501 252256
2014 04 556 252222
2014 05 587 253000
2014 06 595 253599
2014 07 459 254568
2014 08 531 254998
2014 09 598 255998
2014 10 358 256687
2014 11 589 257169
2014 12 456 258859
2015 01 598 259589
2015 02 433 264210
;
run;
data in; set in; c1=lag1(claims);c2=lag2(claims);c3=lag3(claims);
p1=lag1(ptf); p2=lag2(ptf); p3=lag3(ptf);
proc sql;
select y,m,claims,ptf,(( claims+c1+c2+c3)/(ptf+p1+p2+p3)) as myratio
from in;
quit;``````

Good Luck...!!!

Kannan Deivasigamani
Posts: 5,521

## Re: Rolling 12 months, calculating frequence

With SQL, I would prefer:

``````
proc sql;
create table wantSQL as
select a.*, sum(b.claims)/sum(b.ptf) as claimRate
from have as a inner join have as b
on intck("MONTH", mdy(a.maand,1,a.jaar), mdy(b.maand,1,b.jaar))
between -3 and 0
group by a.jaar, a.maand, a.claims, a.ptf;
select * from wantSQL;
quit;``````
PG
Regular Contributor
Posts: 161

## Re: Rolling 12 months, calculating frequence

PG - I seem to learn something new from you almost every day... Thx for the technical contribution....

Kannan Deivasigamani
Super User
Posts: 6,751

## Re: Rolling 12 months, calculating frequence

Here's a DATA step approach that is amenable to adding more variables and a variety of numbers of months:

data want;

set have;

totclaims + claims;

totclaims_L4 + lag4(claims);

totptf + ptf;

totptf_L4 + lag4(ptf);

freq4 = (totclaims - totclaims_L4) / (totptf - totptf_L4);

run;

So if you need 8-month calculations as well, just compute totclaims_L8 + lag8(claims)

Good luck.

Discussion stats
• 5 replies
• 649 views
• 1 like
• 4 in conversation