topic algorithm creation in New SAS User

algorithm creation

Charlotte37 — Tue, 03 Dec 2019 13:34:45 GMT

Hello,

I have a table with the history of insured persons by contract.

For example, the A contract have 2 insured persons valid.

CONTRACT_NUMBER	RANK	NAME_RANK	STATUS	SUBSCRIPTION_DATE	CANCELLATION_DATE	BIRTHDAY_MAIN_INSURED
A	1	main insured	removed	01/10/2005	20/01/2007	01/05/1976
B	1	main insured	valid	01/01/2019		15/09/1955
B	2	spouse	valid	01/06/2019		15/09/1955
C	1	main insured	valid	01/01/2019		04/07/1982
C	2	spouse	valid	01/01/2019		04/07/1982
D	1	main insured	valid	01/03/2009		02/08/1944
D	2	spouse	removed	01/05/2010	15/09/2018	02/08/1944

I have a 2nde table with the amount of contributions :

Age	Individual_Membership	Collective_Membership
Until 29 years old	8	44,8
From 30 to 65 years old included	22,4	44,8
From 66 to 70 years old included	27,2	54,4
From 70	34	68

I want to know the amount of the contributions of each contract for each year.

If a contract has only one insured then the amount is in the column "Individual_Membership" otherwise in the column "Collective_Membership".

if the contract starts in "individually" in the year and ends in "collective" then it is necessary to prorate the amounts.

The transition to the senior slice is 01/01 of the following year

My final table :

CONTRACT_NUMBER	YEAR	AMOUNT
A	2005	2,02
A	2006	8
A	2007	1,23
B	2019	9,23 + 26,27 = 35,5
C	2019	44,8
D	2009	18,78
D	2010	8,94 + 36,52 = 45,46
D	2011	54,4
D	2012	54,4
D	2013	54,4
D	2014	54,4
D	2015	68
D	2016	68
D	2017	68
D	2018	48,07 + 9,97 = 58,04
D	2019	34

Thank you very much for your help

Re: algorithm creation

PaigeMiller — Tue, 03 Dec 2019 13:44:34 GMT

@Charlotte37 wrote:

I have a table with the history of insured persons by contract.

For example, the A contract have 2 insured persons valid.

CONTRACT_NUMBER RANK NAME_RANK STATUS SUBSCRIPTION_DATE CANCELLATION_DATE BIRTHDAY_MAIN_INSURED

A 1 main insured removed 01/10/2005 20/01/2007 01/05/1976

B 1 main insured valid 01/01/2019 15/09/1955

B 2 spouse valid 01/06/2019 15/09/1955

C 1 main insured valid 01/01/2019 04/07/1982

C 2 spouse valid 01/01/2019 04/07/1982

D 1 main insured valid 01/03/2009 02/08/1944

D 2 spouse removed 01/05/2010 15/09/2018 02/08/1944

I'm sorry, I don't see how A has two insured persons.

Re: algorithm creation

Charlotte37 — Tue, 03 Dec 2019 14:46:39 GMT

It's a mistake.

The A contract have 1 insured person valid whereas the others have 2 insured persons valid