topic Re: How can we calculate the amount given and taken by whom using retain and loops? in SAS Programming

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

AnupamaMishra — Wed, 13 May 2020 07:13:27 GMT

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?

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

s_lassen — Wed, 13 May 2020 09:56:24 GMT

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;