Rolling 12 months, calculating frequence

[Leonard32]

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.

Thanks in advance!

Leonard

[PGStats]

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;

[kannand]

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

[PGStats]

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;

[kannand]

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

[Astounding]

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.