How to Allocate Recovery Amount Value to loss amount

[anshul_9]

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

[Kurt_Bremser]

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.

[Tom · Accepted solution]

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

[Ksharp]

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;