How can we calculate the amount given and taken by whom using retain and loops?

[AnupamaMishra]

I have a dataset with the list of givers and takers present in one column and the amount that is given (the negative values) Or taken (the positive value) by them in another column.

The amount deviation is such that its total sum is equals to 0.

I want to create a dataset consisting of from, amount, to as variables.

The selection of the members should be in sequence for from and to.

Input dataset :
Member Deviation
Aus -2000
Br 3000
Nz -10000
In 4000
No 5000
Br  -3000.36
Nz 2000.36
In -4000.53
No 5000.53

Output dataset:
From   Amount   To
Aus     2000       Br
Nz      1000        Br
Nz      4000        In
Nz      5000        No

Br       2000.36   Nz

Br       1000        No

In        4000.53  No

Can anyone help solving it using retain and loops?

[s_lassen · Accepted solution]

That was a fun challenge! Here is a solution that works, at least for the data given:

data have;
  length member $3;
  input Member Deviation;
cards;
Aus -2000
Br 3000
Nz -10000
In 4000
No 5000
Br  -3000.36
Nz 2000.36
In -4000.53
No 5000.53
;run;

data want;
  set have(where=(Deviation<0) rename=(Member=From));
  retain Remainder 0;
  if Remainder>0 then do;
    Amount=Min(Remainder,-Deviation);
    output;
    end;
  Remainder=Remainder+Deviation;
  do while(not done and Remainder<0);
    set have(where=(Deviation>0) rename=(Member=To)) end=done;
    Amount=min(-Remainder,Deviation);
    output;
    Remainder=Remainder+Deviation;
    end;
  Keep From Amount To;
run;