topic Re: To calculate values based on values present in other dataset column in SAS Programming

To calculate values based on values present in other dataset column

AnupamaMishra — Tue, 05 May 2020 17:25:11 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

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

Re: To calculate values based on values present in other dataset column

ballardw — Tue, 05 May 2020 18:03:17 GMT

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.

Re: To calculate values based on values present in other dataset column

PGStats — Tue, 05 May 2020 18:53:58 GMT

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

Re: To calculate values based on values present in other dataset column

AnupamaMishra — Wed, 06 May 2020 07:15:09 GMT

Thank you so much for the solution 😊 @PGStats

Re: To calculate values based on values present in other dataset column

Ksharp — Wed, 06 May 2020 11:37:48 GMT

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;

Re: To calculate values based on values present in other dataset column

AnupamaMishra — Wed, 13 May 2020 06:46:58 GMT

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?

Re: To calculate values based on values present in other dataset column

Ksharp — Wed, 13 May 2020 10:55:40 GMT

Just multiply 100 .

Deviation=int(Deviation*100) ;