topic Re: calculations across different rows and datasets in SAS Programming

calculations across different rows and datasets

Stan76 — Wed, 26 Jul 2017 14:26:01 GMT

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.

Re: calculations across different rows and datasets

ballardw — Wed, 26 Jul 2017 17:32:10 GMT

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

Re: calculations across different rows and datasets

Ksharp — Thu, 27 Jul 2017 13:57:14 GMT

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;
read all var{month month2};
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;