topic Re: Rolling 12 months, calculating frequence in SAS Procedures

Rolling 12 months, calculating frequence

Leonard32 — Tue, 27 Oct 2015 21:08:46 GMT

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

Re: Rolling 12 months, calculating frequence

PGStats — Tue, 27 Oct 2015 22:04:43 GMT

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;

Re: Rolling 12 months, calculating frequence

kannand — Tue, 27 Oct 2015 21:46:25 GMT

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

Re: Rolling 12 months, calculating frequence

PGStats — Tue, 27 Oct 2015 22:19:09 GMT

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;

Re: Rolling 12 months, calculating frequence

kannand — Tue, 27 Oct 2015 22:21:17 GMT

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

Re: Rolling 12 months, calculating frequence

Astounding — Tue, 27 Oct 2015 22:44:59 GMT

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.