Hello Fellow SAS Developers,
I am trying to merge two tables:-
Loss table which consists of two columns i.e ID and Loss amount
ID Loss_amount
100 10,000
100 15,000
100 5,000
Recovery Table which consists of two columns i.e ID and recovery amount
ID Recovery_amount
100 20,000
I need to combine both the tables and the output should be in such a manner that recovery amount
is the maximum of loss amount and the remaining amount is passed on to the next record.
For eg:-
ID Loss_amount Recovery_amount
100 10,000 10,000
100 15,000 10,000
100 5,000 0
You need another variable.
data loss ;
input ID Loss_amount :comma.;
cards;
100 10,000
100 15,000
100 5,000
;
data recovery;
input ID Recovery_amount :comma.;
cards;
100 20,000
;
data want;
merge loss(in=in1) recovery(in=in2) ;
by id;
recovered = min(loss_amount,recovery_amount);
output;
recovery_amount=max(0,recovery_amount-recovered);
run;
Loss_ Recovery_ Obs ID amount amount recovered 1 100 10000 20000 10000 2 100 15000 10000 10000 3 100 5000 0 0
What is the formula for calculating the new recovery_amount? In the first observation, you subtract the loss (10000) from 20000, leaving 10000. So in the second observation, 10000 - 15000 should result in 0, as you obviously want to stop at zero.
You need another variable.
data loss ;
input ID Loss_amount :comma.;
cards;
100 10,000
100 15,000
100 5,000
;
data recovery;
input ID Recovery_amount :comma.;
cards;
100 20,000
;
data want;
merge loss(in=in1) recovery(in=in2) ;
by id;
recovered = min(loss_amount,recovery_amount);
output;
recovery_amount=max(0,recovery_amount-recovered);
run;
Loss_ Recovery_ Obs ID amount amount recovered 1 100 10000 20000 10000 2 100 15000 10000 10000 3 100 5000 0 0
data loss ;
input ID Loss_amount :comma.;
cards;
100 10,000
100 15,000
100 5,000
;
data recovery;
input ID Recovery_amount :comma.;
cards;
100 20,000
;
data want;
merge loss recovery ;
by id;
if first.id then cum_lost=0 ;
cum_lost+Loss_amount;
if Recovery_amount>=cum_lost then want=Loss_amount;
else want=ifn(cum_lost-Recovery_amount<Loss_amount,Loss_amount-cum_lost+Recovery_amount,0);
run;
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.