DATA Step, Macro, Functions and more

Calculate expected amount with different terms of payment

Reply
Contributor
Posts: 36

Calculate expected amount with different terms of payment

Hi everybody,

 

I work for an insurance company. We sell (among other things) car insurances. Our customers can choose in how many terms they want to pay the insurance:

  • Once a year                       (complete premium)
  • Every six months            (premium split in half)
  • Each quarter                     (premium split in four)
  • Every month                     (premium split in twelve)

 

I’ve created a set of our current portfolio. I would like to calculate how much premium we expect to receive each month, if nothing changes in our portfolio of course J. I’ve added a fictive set with two months below.

contract_renewal_month product paymentterm count premium_year  monthly term  terms  premium_contract_renewal  premium_payment
1 car insurance Half yearly 100 45640  €                     38,03 6  €                                      22.820,00 value contract_renewal_month 6
1 car insurance yearly 150 65844  €                     36,58 12  €                                      65.844,00 -
1 car insurance quarterly 25 9344  €                     31,15 4  €                                        3.114,67 sum values contract_renewal_month 4,7 and 10
1 car insurance monthly 350 109994  €                     26,19 1  €                                        9.166,17 sum values contract_renewal_month 2 t/m 12
2 car insurance Half yearly 150 59045  €                     32,80 6  €                                      29.522,50 value contract_renewal_month 7
2 car insurance yearly 150 66899  €                     37,17 12  €                                      66.899,00 -
2 car insurance quarterly 150 55555  €                     30,86 4  €                                      18.518,33 sum values contract_renewal_month 5,8 and 11
2 car insurance monthly 150 55874  €                     31,04 1  €                                        4.656,17 sum values contract_renewal_month 3 t/m 12 and 1

ect 

 

monthly_term = (premium_year)/(count)/12

premium_contract_renewal = (count)*(monthly_term)*(terms)

 

How can I calculate the results as descibed in column ‘premium_payment’ for each contract_renewal_month?

 

Thank you very much for your time.

Super User
Posts: 9,681

Re: Calculate expected amount with different terms of payment

Your data is messed up . Can you post simple data and explain what you want ?

I notice :

 

 

data _null_;
premium_year=45640;
terms=6;
monthly_term = premium_year/12;
premium_contract_renewal = monthly_term*terms;
put _all_;
run;
Contributor
Posts: 36

Re: Calculate expected amount with different terms of payment

Attempt two.

DATA HAVE;
	infile datalines delimiter=',';
	input contr_renew_month product $ payment_term $ count amt_policy amt_month terms amt_contr_ren;
	datalines;
1,	car,	half,	100,	45640,	38.03, 	6,	22820 
1,	car,	year,	150,	65844,	36.58, 	12,	65844 
1,	car,	qrt,	25,	9344,	31.15, 	4,	3114.67 
1,	car,	mnth,	350,	109994,	26.19, 	1,	9166.17 
2,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
2,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
2,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
2,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
;
RUN;

 

I want to create an extra column --> amt_payment.

 

In 'contract_renewal_mont' 1 we don't just receive the payments of the policies where the contract is renewed. We also recieve payments of customers who pay in terms. In this example in month 1:

SUM(amt_contr_renewal) of 'half' of contr_renew_mnth 6 when then customer has a payment_term 'half' 

SUM(amt_contr_renewal) of 'qrt' of contr_renew_mnth 4,7,10 when then customer has a payment_term 'qrt' 

SUM(amt_contr_renewal) of 'mnth' of contr_renew_mnth 2 - 12 when then customer has a payment_term 'mnth' 

 

English is not my native language, so I hope you understand my intentions.

Super User
Posts: 9,681

Re: Calculate expected amount with different terms of payment

What kind of output would you like to see ? "contr_renew_mnth 4,7,10" --- here 4,7,10 are all from TERMS variable ?
Contributor
Posts: 36

Re: Calculate expected amount with different terms of payment

Yes.

In january we (also) receive de quarterly payments for policies with a renewal_month AND with term 'qrt' in 4(apr), 7(jul) and 10(oct).

So i need a way to sum these amount.

 

In february we receive de quarterly payments for policies in 5(may), 8(aug) and 11(nov).

ect ect.

Super User
Posts: 17,837

Re: Calculate expected amount with different terms of payment

For each obs use an explicit OUTPUT statement to print out an observation with a variable indicating the payment amount and month you'll receive it. 

 

Then use proc means/SQL to summarize by months. 

 

I dont quitw understand your data but for example if you have someone who pays twice a year create two rows, each with a variable that indicates month of payment, ie month=1 and month=6

 

Heres an example of what the code may look like. I've assumed that renewal is actually a date because that makes more sense than just a month. 

Data expand;
Set have;

If type='half' then do;
expected = premium/2;
Output; 
Month=intnx('month', renewal_date, 6, 'm');
Output;
End;

Run;
Contributor
Posts: 36

Re: Calculate expected amount with different terms of payment

Thank you for your trouble.

It's not what i'm looking for. Perhaps something gets lost in the translation.

Super User
Posts: 17,837

Re: Calculate expected amount with different terms of payment

It will answer your question. How is it not what your looking for?

Super User
Posts: 9,681

Re: Calculate expected amount with different terms of payment

Sorry. I still am not able to get your point . Assuming I understand what you mean.

 

DATA HAVE;
	infile datalines delimiter=', ' expandtabs;
	input contr_renew_month product $ payment_term $ count amt_policy amt_month terms amt_contr_ren;
	datalines;
1,	car,	half,	100,	45640,	38.03, 	6,	22820 
1,	car,	year,	150,	65844,	36.58, 	12,	65844 
1,	car,	qrt,	25,	9344,	31.15, 	4,	3114.67 
1,	car,	mnth,	350,	109994,	26.19, 	1,	9166.17 
2,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
2,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
2,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
2,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
3,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
3,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
3,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
3,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
4,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
4,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
4,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
4,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
5,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
5,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
5,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
5,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
6,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
6,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
6,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
6,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
7,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
7,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
7,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
7,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
8,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
8,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
8,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
8,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
9,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
9,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
9,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
9,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
10,	car,	half,	150,	59045,	32.80, 	6,	29522.50 
10,	car,	year,	150,	66899,	37.17, 	12,	66899.00 
10,	car,	qrt,	150,	55555,	30.86, 	4,	18518.33 
10,	car,	mnth,	150,	55874,	31.04, 	1,	4656.17 
;
RUN;

proc sql;
select *,case 
 when(payment_term='half') then 
  (select amt_contr_ren from have where contr_renew_month=a.contr_renew_month+5 and payment_term=a.payment_term)
 when(payment_term='qrt') then
  (select sum(amt_contr_ren) from have where (contr_renew_month=a.contr_renew_month+3
  or contr_renew_month=a.contr_renew_month+6 or contr_renew_month=a.contr_renew_month+9) and payment_term=a.payment_term)
 when(payment_term='mnth') then
  (select sum(amt_contr_ren) from have where contr_renew_month between a.contr_renew_month+1 and 12 and payment_term=a.payment_term)
 else . 
end as amt_payment
 from have as a;
quit;
Super User
Posts: 17,837

Re: Calculate expected amount with different terms of payment

What happens when renewal month is 10 and payments are QTR? The year needs to be accounted for somehow. 

Contributor
Posts: 36

Re: Calculate expected amount with different terms of payment

Hi all, A co-worker found the answer (helped I could explain it in Dutch). I forgot to update the post. Beter late then never..... DATA work.result ; SET work._temp_agg; ATTRIB premium_contract_renewal LENGTH= 8 FORMAT=20.2; ARRAY amt {12} maandbedrag1-maandbedrag12 ; SELECT (betaaltermijn); WHEN ('Month') DO; terms=1; termijnen=12; END; WHEN ('Quarter') DO; terms=3; termijnen=4; END; WHEN ('Half year') DO; terms=6; termijnen=2; END; WHEN ('Year') DO; terms=12; termijnen=1; END; OTHERWISE; END; premium_contract_renewal = bestandsamt / termijnen; DO i = 1 TO 12; IF i = prolongatiemaand THEN bedrag[i] = premium_contract_renewal; * prolongatieamt ; ELSE IF MOD(i - prolongatiemaand, terms ) = 0 THEN amt[i] = premium_contract_renewal; ELSE amt[i] = .; *PUT _ALL_; END; RUN; (I suddenly have no options in my post. So I can't put it in a nice sas-look&feel by using 'the running man')
Contributor
Posts: 36

Re: Calculate expected amount with different terms of payment

Due to proxy-problems I do not have any options on this website. The prev. post isn't readable.....But I can't changeit or delete it. I'm trying to fix it so I can correctly show the post.
Super User
Posts: 9,681

Re: Calculate expected amount with different terms of payment

Click HTML tag, and put the following in it.
 your sas code here 
Contributor
Posts: 36

Re: Calculate expected amount with different terms of payment

I know.... Our system administrators did 'something' to the proxy, I can't see (or interact) the buttons on de webpage.
Super User
Posts: 9,681

Re: Calculate expected amount with different terms of payment

Check attachment .


x.png
Ask a Question
Discussion stats
  • 14 replies
  • 526 views
  • 0 likes
  • 3 in conversation