DATA Step, Macro, Functions and more

calculations across different rows and datasets

Reply
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 arrsmonth1 month2month3
May01000880 B? A
May1400420 C 
May2200220 D 
May3100130 E 
May450100 F 
June0780  
June1260  
June2180  
June380  
June430  

 

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:

 

month3to0to1to2to3to4
from090% V10%0%0%0%
from125% W70%5%0%0%
from215% X10%50%10%0%
from315% Y0%5%30%60%
from47% Z1%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: 10,538

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: 9,687

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;
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;
Ask a Question
Discussion stats
  • 2 replies
  • 59 views
  • 0 likes
  • 3 in conversation