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:
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.
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;
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.
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.
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;
Thank you for your trouble.
It's not what i'm looking for. Perhaps something gets lost in the translation.
It will answer your question. How is it not what your looking for?
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;
What happens when renewal month is 10 and payments are QTR? The year needs to be accounted for somehow.
your sas code here
Check attachment .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.