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
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;
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...!!!
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 - I seem to learn something new from you almost every day... Thx for the technical contribution....
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.