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;
... View more
