turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- calculations across different rows and datasets

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-26-2017 10:26 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Stan76

07-26-2017 01:32 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Stan76

07-27-2017 09:57 AM

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;
```