How to compute proportion rate for two different rows.

[adil256]

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;

[Ksharp]

You did not post the result yeah.

Can you post an example (how to calculate) to explain your question detailly .

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
 ;
proc sort data=table1;
 by product;
run;
data temp;
 ina=0;
 merge table1(in=ina) table2;
 by product;
 if ina;
run;
proc sort data=temp;
 by Groupe_tarification  Sexe warranty Smoker;
run;
proc sort data=table3;
 by Groupe_tarification  Sexe warranty Smoker;
run;
data temp1;
 ina=0;
 merge temp(in=ina) table3;
 by Groupe_tarification  Sexe warranty Smoker;
 if ina;
run;
proc sort data=temp1;
 by name;
run;
data temp2;
 ina=0;
 merge temp1(in=ina) Mortality_Table_;
 by name;
 if ina;
run;
proc sort data=temp2;
 by Contract  Twin;
run;
proc sql;
select *,Amount*q_x/sum(q_x) as Amount_Split_Per_Twin
 from temp2
  group by Contract , Twin;
quit;

[adil256 · Accepted solution]

Hi thank you. I think I've achieved what I wanted to do. The code is a little bit cumbersome but it works.  Don't know if there is a better way to do but it works :). I think I will draw from your code to merge different table thank u very much.

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 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 temp (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;

proc sort data=temp;
by contract;
run;

data temp1 (keep=contract sum_qx);
set temp;
by contract;
if first.contract then sum_qx=0;
sum_qx+q_x;
if last.contract;
run;

proc sort data=temp1;
by contract;
run;

data temp2;
merge temp temp1;
by contract;
run;


data table_final;
set temp2;
if twin=2 then Amount_split=Amount*(q_x/sum_qx);
else amount_split=amount;
run;

[adil256]

@Ksharp Hi Ksharp, i've a question for you. I would like to know why you initialize the variable ina in the data merge? It's done automatically with the statement (in=ina)?

thx

[Ksharp]

I want only keep the obs from the first table , drop others from second table.
E.X.
table A
id
1

table B
id 
1
1

if you don't specify ina=0 before merge, then you will get two obs ,not only one.

[adil256]

@Ksharp thx 🙂