turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Calculate expected amount with different terms of ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-19-2016 04:08 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Matthijs

07-19-2016 04:42 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

07-19-2016 05:20 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Matthijs

07-19-2016 05:47 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

07-19-2016 05:52 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Matthijs

07-19-2016 06:33 AM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

07-19-2016 07:31 AM

Thank you for your trouble.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Matthijs

07-19-2016 09:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Matthijs

07-19-2016 09:15 PM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

07-20-2016 01:40 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Matthijs

09-15-2016 01:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Matthijs

09-15-2016 01:52 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Matthijs

09-15-2016 05:07 AM

Click HTML tag, and put the following in it.

your sas code here

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

09-15-2016 05:09 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Matthijs

09-15-2016 05:38 AM

Check attachment .