New Contributor
Posts: 4

# calculations across different rows and datasets

Is it possible create a value based on variables in separate rows in two datasets?

I have a data table that contains info a little like this:

 month arrs month1 month2 month3 May 0 1000 880 B ? A May 1 400 420 C May 2 200 220 D May 3 100 130 E May 4 50 100 F June 0 780 June 1 260 June 2 180 June 3 80 June 4 30

I need to calculate forecast values for all of the blank cells by applying proportions of accounts from the previous month.  The proportions will be different each month, for example to calculate the value for cell A i will apply proportions from the month 3 table below:

 month3 to0 to1 to2 to3 to4 from0 90% V 10% 0% 0% 0% from1 25% W 70% 5% 0% 0% from2 15% X 10% 50% 10% 0% from3 15% Y 0% 5% 30% 60% from4 7% Z 1% 1% 1% 90%

So the calculation for value A is A= (B*V)+(C*W)+(D*X)+(E*Y)+(F*Z)

I've struggled with tabulates and transposes to try and get all the variables from both datasets on one line, but to no avail.

Super User
Posts: 13,583

## Re: calculations across different rows and datasets

How do we know that you use the values of to0 in the second set with the month2 values in the first?

It will help if you provide enough data to work a complete example with both starting data sets and the actual desired result for that example data.

You indicate a single desired calculation but your requirement is "for all of the blank cells by applying proportions of accounts from the previous month".

So: what would the value for the second row (the one under the A) look like?

where would the values of the June variables for Month2 come from?

Do the variables to1 through to4 play a role? If so what?

You may find going forward that anytime you are dealing with dates then date values are much more useful than some not-quite-obvious offset, which I am guessing that From0, From1 and the to0, to1 etc may be. It appears that pulling the suffix from the From and aligning with the ARRS variable might be part of the task but you haven't said such. That might make calculaing each piece of (B*V)+(C*W)+(D*X)+(E*Y)+(F*Z) quite easy. But the rules around what

Super User
Posts: 10,787

## Re: calculations across different rows and datasets

It would be very easy if you are using IML code.

``````data have;
infile cards truncover expandtabs;
input month \$	arrs	month1 	month2;
cards;
May	0	1000	880
May	1	400	420
May	2	200	220
May	3	100	130
May	4	50	100
June	0	780	 430
June	1	260	 436
June	2	180	 678
June	3	80	 875
June	4	30  324
;
run;
data month3;
infile cards truncover expandtabs;
input month3 \$ (to0	to1	to2	to3	to4) (: percent7.);
cards;
from0	90% 	10%	0%	0%	0%
from1	25% 	70%	5%	0%	0%
from2	15% 	10%	50%	10%	0%
from3	15% 	0%	5%	30%	60%
from4	7% 	1%	1%	1%	90%
;
run;

proc iml;
use have nobs nobs;
close;
use month3;
read all var _num_ into x;
close;

temp=uniqueby(month);
start_end=temp||t(remove(temp-1,1)||nobs);

do i=1 to nrow(start_end);
idx=start_end[i,1]:start_end[i,2];
t=month2[idx];
do j=1 to nrow(t);
month3=month3//sum(t#x[,j]);
end;
end;
create result var {month3};
append;
close;
quit;

data want;
merge have result;
run;``````
Discussion stats
• 2 replies
• 133 views
• 0 likes
• 3 in conversation