Hello, I need have a cumulative sum of the next couple of weeks (dependent on a factor variable). I can do this with SQL, but is bad for performance and does not run on large data sets. Basically I want the sum of the next X amount of weeks (based on the column WKfactor) for each unique product and location. Original data does not have the uniquekey, but created it for sql code. UniqueKey Product Location Week WKFactor Value desired sum A1 A 1 7-Jan-19 4 43 94 <-sum of 'value' for the next for the next 4 weeks (comes from WK factor) of product A, Location 1 A1 A 1 14-Jan-19 5 2 76 <-sum of 'value' for the next for the next 45weeks (comes from WK factor) of product A, Location 1 A1 A 1 21-Jan-19 4 27 74 <-sum of 'value' for the next for the next 4 weeks (comes from WK factor) of product A, Location 1 A1 A 1 28-Jan-19 4 22 47 <-sum of 'value' for the next for the next 3 weeks (because week factor is 4, but there are only 3 weeks in data set remaining) of product A, Location 1 A1 A 1 4-Feb-19 4 22 25 A1 A 1 11-Feb-19 4 3 3 A2 A 2 7-Jan-19 2 31 66 A2 A 2 14-Jan-19 2 35 76 A2 A 2 21-Jan-19 2 41 47 A2 A 2 28-Jan-19 2 6 6 B1 B 1 7-Jan-19 3 22 101 B1 B 1 14-Jan-19 2 34 79 B1 B 1 21-Jan-19 3 45 97 B1 B 1 28-Jan-19 5 22 52 B1 B 1 4-Feb-19 2 30 30 This is the SQL code that gives me the desired output, but takes too long doing the inner joins on such a large data set. any better ways? proc sql;
create table work.desire as
select uniquekey, product, location, week, wkfactor, value,
(select sum(value), from work.have where uniquekey=a.uniquekey and week >= a.week and week <= intnx('week', a.week, a.wkfactor)) as desiredsum
from work.have as a;
... View more
