Hi everyone again, I need to split the amount value based on proportional q_x among two rows only for twin=2 in Table1. First step, I need to know the q_x therefore I retrieve the information in Mortality_Table_ thanks to the code of @Kurt_Bremser. Then I need to compute the splited amount based on proportional q_x . Therefore i've to sum q_x through two different rows then go back to the first rows of twin contract and compute the splited amount then go to the second row and compute the contrary. Example for contrat n°2: ==> 2000*(q_x1/q_x1+q_x2) for the first row and 2000*(q_x2/q_x1+q_x2) or 2000( 1-q_x1/q_x1+q_x2) I don't know if it's clear enough for you guys if it's not i'll try to do so :-). data Table1;
input Contract $ Twin Product Sexe warranty Smoker Amount Age ;
infile datalines missover;
datalines;
1 1 1 1 0 1 1000 25
2 2 3 1 1 1 2000 21
2 2 3 0 1 1 2000 19
3 1 3 0 1 1 1500 23
4 1 3 1 0 1 2999 22
5 1 4 1 0 0 5565 21
6 2 1 1 0 0 1235 19
6 2 1 0 0 1 1235 24
7 1 2 1 0 1 4565 25
8 1 1 1 1 1 9745 22
;
data Table2;
input Product Groupe_tarification $;
infile datalines missover;
datalines;
1 A2
2 A1
3 A1
4 A2
5 A2
6 A2
7 A1
8 A1
9 A2
10 A1
;
data Table3;
input Groupe_tarification $ Name $ Sexe warranty Smoker rate_ab;
infile datalines missover;
datalines;
A1 MK 0 0 0 0.25
A1 MK 1 0 0 0.1
A1 MK 0 1 0 0.35
A1 UNI 0 0 1 1
A1 UNI 1 1 0 1.25
A1 MK 0 1 1 0.75
A1 UNI 1 0 1 0.15
A1 UNI 1 1 1 -0.35
A2 UNI 0 0 0 -0.5
A2 MK 1 0 0 0.25
A2 UNI 0 1 0 0.95
A2 UNI 0 0 1 -0.25
A2 MK 1 1 0 0.45
A2 MK 0 1 1 0.85
A2 UNI 1 0 1 0.15
A2 MK 1 1 1 0
;
data Mortality_Table_;
input Name $ Age q_x;
infile datalines missover;
datalines;
MK 19 0.05
MK 20 0.065
MK 21 0.07
MK 22 0.085
MK 23 0.089
MK 24 0.091
MK 25 0.092
UNI 19 0.045
UNI 20 0.046
UNI 21 0.047
UNI 22 0.05
UNI 23 0.52
UNI 24 0.57
UNI 25 0.599
;
data Table_Final;
input Contract Twin Product Sexe warranty Smoker Amount Age Amount_Split_Per_Twin;
infile datalines missover;
datalines;
1 1 1 1 0 1 1000 25
2 2 2 0 1 1 2000 21
2 2 3 0 1 1 2000 19
3 1 3 0 1 1 1500 23
4 1 3 1 0 1 2999 22
5 1 4 1 0 0 5565 21
6 2 4 0 0 0 1235 19
6 2 1 0 0 1 1235 24
7 1 2 1 0 1 4565 25
8 1 1 1 1 1 9745 22
;
run;
data cntlin (keep=fmtname type start label);
set table2;
fmtname = 'product';
start = put(Product,2.);
label = Groupe_tarification;
type = 'N';
run;
proc format library=work cntlin=cntlin;
run;
data cntlin;* (keep=start fmtname type label);
set table3;
start = strip(Groupe_tarification) !! put(Sexe,1.) !! put(warranty,1.) !! put(Smoker,1.);
label = Name;
type = 'C';
fmtname = 'Name';
run;
proc format library=work cntlin=cntlin;
run;
data cntlin (keep=start fmtname type label);
set Mortality_Table_;
start=strip(Name) !! strip(put(age,best.));
label=put(q_x,best.);
type='C';
fmtname='q_x';
run;
proc format library=work cntlin=cntlin;
run;
data want (drop= tarcode Name tarcode2);
set table1;
tarcode = strip(put(product,product.)) !! put(Sexe,1.) !! put(warranty,1.) !! put(Smoker,1.);
Name = put(tarcode,$name.);
tarcode2=strip(Name)!!put(age,2.);
q_x=input(put(tarcode2,$q_x.),best.);
run;
... View more
