BookmarkSubscribeRSS Feed
Matthijs
Obsidian | Level 7

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.

14 REPLIES 14
Ksharp
Super User

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;
Matthijs
Obsidian | Level 7

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.

Ksharp
Super User
What kind of output would you like to see ? "contr_renew_mnth 4,7,10" --- here 4,7,10 are all from TERMS variable ?
Matthijs
Obsidian | Level 7

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.

Reeza
Super User

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;
Matthijs
Obsidian | Level 7

Thank you for your trouble.

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

Reeza
Super User

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

Ksharp
Super User

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;
Reeza
Super User

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

Matthijs
Obsidian | Level 7
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')
Matthijs
Obsidian | Level 7
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.
Ksharp
Super User
Click HTML tag, and put the following in it.
 your sas code here 
Matthijs
Obsidian | Level 7
I know.... Our system administrators did 'something' to the proxy, I can't see (or interact) the buttons on de webpage.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1748 views
  • 0 likes
  • 3 in conversation