Adjustment of amount between two tables

[ranjit1]

Table data_1
CIF ACNO AMT
123 14 100
123 15 200
123 16 200
159 17 100
159 18 200
159 19 200
159 20 200
159 30 200

Table data_2
CIF AMT
123 -400
159 -1500

Please guide for SAS procedure for the following: DATA_1.AMT should be mapped BY CIF and adjusted by DATA_2.AMT and the both table updated with NEW_AMT as under:

 Table data_1
CIF ACNO AMT  NEW_AMT
123 14 100  0
123 15 200  0
123 16 200  100
159 17 100  0
159 18 200  0
159 19 200  0
159 20 200  0
159 30 200  0

Table data_2

CIF AMT NEW_AMT
123 -400 0
159 -1500 -600

Many thanks in advance

[RW9 · Accepted solution]

Post test data in the form of a datastep using the code window (its the {i} above post area).

As such it seems that you want to merge the big number from table two onto the last record of the first table, and take that number off.  If so:

data want;
  merge data_1 data_2 (rename=(amt=_amt));
  by cif;
run;

data want;
  set want;
  by cif;
  retain running;
  running=ifn(first.cif,amt,sum(running,amt));
  if last.cif then new_amt=running-_amt;
run;