To calculate values based on values present in other dataset column

[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

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

[ballardw]

And how are we supposed to get the from/to for any given amount?

You need to provide a lot more details of the process and likely a larger example data set.

[PGStats · Accepted solution]

This looks like an optimal flow problem

data have;
input Member$ Deviation;
datalines;
Aus -2000
Br 3000
Nz -10000
In 4000
No 5000
;

/* Create every possible link, all with equal cost */
proc sql;
create table links as
select a.member as from, b.member as to, 1 as weight
from have as a inner join have as b on a.member ne b.member;
quit;

proc optnet data_links=links data_nodes=have graph_direction=directed 
	out_links=want(drop=weight rename=(from=to to=from mcf_flow=amount) where=(amount>0));
data_nodes_var node=member weight=deviation;
mincostflow;
run;

proc print data=want noobs; run;

to 	from 	amount
Br 	Aus 	2000
Br 	Nz 	1000
In 	Nz 	4000
No 	Nz 	5000

[AnupamaMishra]

Thank you so much for the solution 😊 @PGStats 

[Ksharp]

Are you processing STOCK data(buy or sell  share) ?

data have;
input Member$ Deviation;
datalines;
Aus -2000
Br 3000
Nz -10000
In 4000
No 5000
;
data positive;
 set have(where=(Deviation>0));
 do i=1 to deviation;
  output;
 end;
keep member;
run;
data negative;
 set have(where=(Deviation<0));
 do i=1 to -deviation;
  output;
 end;
keep member;
run;
data temp;
 merge positive negative(rename=(member=m));
 if member ne lag(member) or m ne lag(m) then group+1;
run;
proc sql;
create table want as
select group,max(m) as from,max(member) as to,count(*) as amount
 from temp
  group by group;
quit;

[AnupamaMishra]

If the dataset is like below:

Member Deviation 
Aus -2000
Br 3000
Nz -10000
In 4000
No 5000
Nz -5000.56
Br 3000.56
In -2000.63
Eu 4000.63

and I want output like below:

From  Amount To

Aus   2000       Br

Nz     1000       Br

Nz      4000      In

Nz      5000      No

Nz      3000.56 Br

Nz      2000      Eu

In       2000.63 Eu

How can we do it using retain and some loops?

[Ksharp]

Just multiply 100 .

Deviation=int(Deviation*100) ;