topic Re: Calculate expected amount with different terms of payment in SAS Programming

Calculate expected amount with different terms of payment

Matthijs — Tue, 19 Jul 2016 08:08:15 GMT

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.

Re: Calculate expected amount with different terms of payment

Ksharp — Tue, 19 Jul 2016 08:42:31 GMT

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;

Re: Calculate expected amount with different terms of payment

Matthijs — Tue, 19 Jul 2016 09:20:48 GMT

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.

Re: Calculate expected amount with different terms of payment

Ksharp — Tue, 19 Jul 2016 09:47:25 GMT

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

Re: Calculate expected amount with different terms of payment

Matthijs — Tue, 19 Jul 2016 09:52:21 GMT

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.

Re: Calculate expected amount with different terms of payment

Reeza — Tue, 19 Jul 2016 10:33:10 GMT

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;

Re: Calculate expected amount with different terms of payment

Matthijs — Tue, 19 Jul 2016 11:31:26 GMT

Thank you for your trouble.

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

Re: Calculate expected amount with different terms of payment

Reeza — Tue, 19 Jul 2016 13:11:36 GMT

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

Re: Calculate expected amount with different terms of payment

Ksharp — Wed, 20 Jul 2016 01:15:29 GMT

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;

Re: Calculate expected amount with different terms of payment

Reeza — Wed, 20 Jul 2016 05:40:18 GMT

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

Re: Calculate expected amount with different terms of payment

Matthijs — Thu, 15 Sep 2016 05:42:35 GMT

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')

Re: Calculate expected amount with different terms of payment

Matthijs — Thu, 15 Sep 2016 05:52:21 GMT

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.

Re: Calculate expected amount with different terms of payment

Ksharp — Thu, 15 Sep 2016 09:07:01 GMT

Click HTML tag, and put the following in it.

 your sas code here

Re: Calculate expected amount with different terms of payment

Matthijs — Thu, 15 Sep 2016 09:09:11 GMT

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

Re: Calculate expected amount with different terms of payment

Ksharp — Thu, 15 Sep 2016 09:38:25 GMT

Check attachment .