Hi all, what I'm tryin to do is calculate the AR difference between months and offset based on the weeknum.
Below is my code which is working, however I need it to cut off and not calculate unless the Brand, State and Key all match from the previous record. Incorrect ARCalcs are output lines: 9-12, 17-20. The values for diff and arcalc should be '.' (the same as output lines 1-4).
Hopefully this all makes sense. If not please ask away.
data have;
input Brand $ State $ Key AR commax8. drevmo:mmddyy10. week:mmddyy10. weeknum month;
format drevmo mmddyy10. week mmddyy10.;
datalines;
ac ca 2 10000.00 01/01/2020 01/05/2020 1 1
ac ca 2 10000.00 01/01/2020 01/12/2020 2 1
ac ca 2 10000.00 01/01/2020 01/19/2020 3 1
ac ca 2 10000.00 01/01/2020 01/26/2020 4 1
ac ca 2 20000.00 02/01/2020 02/02/2020 1 2
ac ca 2 20000.00 02/01/2020 02/09/2020 2 2
ac ca 2 20000.00 02/01/2020 02/16/2020 3 2
ac ca 2 20000.00 02/01/2020 02/23/2020 4 2
ac ca 3 1000 01/01/2020 01/05/2020 1 1
ac ca 3 1000 01/01/2020 01/12/2020 2 1
ac ca 3 1000 01/01/2020 01/19/2020 3 1
ac ca 3 1000 01/01/2020 01/26/2020 4 1
ac ca 3 2000 02/01/2020 02/02/2020 1 2
ac ca 3 2000 02/01/2020 02/09/2020 2 2
ac ca 3 2000 02/01/2020 02/16/2020 3 2
ac ca 3 2000 02/01/2020 02/23/2020 4 2
bb ca 2 100 01/01/2020 01/05/2020 1 1
bb ca 2 100 01/01/2020 01/12/2020 2 1
bb ca 2 100 01/01/2020 01/19/2020 3 1
bb ca 2 100 01/01/2020 01/26/2020 4 1
bb ca 2 200 02/01/2020 02/02/2020 1 2
bb ca 2 200 02/01/2020 02/09/2020 2 2
bb ca 2 200 02/01/2020 02/16/2020 3 2
bb ca 2 200 02/01/2020 02/23/2020 4 2
;
run;
proc sort data=have; by brand state key descending week ; run;
data diff;
set have;
by brand state key descending month;
retain diff maxweeknum;
if first.month then do;
diff = ar-lag(ar);
maxweeknum=weeknum;
end;
run;
data want;
set diff;
arcalc = ar - ((diff/maxweeknum)*weeknum);
run;
in the data diff section if I try filtering down further e.g.
data diff;
set have;
by brand state key descending month;
retain diff maxweeknum;
if first.month then do;
if brand=lag(brand) then do;
diff = ar-lag(ar);
maxweeknum=weeknum;
end;
end;
run;
It completely breaks the calculation and doesn't work as I am expecting it to.
You are conditionally running the LAG() function call. Since you are calling it inside that IF first.month test it is calculating returning the first value of the previous month. Not the value of the previous record, which would be the last value of the previous month.
For now let's assume that is what you actually want to do.
It sounds like you just need to add more code to "forget' that lagged value when you are starting an new group.
So add a line to set DIFF missing when you are starting a new group.
data diff;
set have;
by brand state key descending month;
retain diff maxweeknum;
if first.month then do;
diff = ar-lag(ar);
if first.key then diff=.;
maxweeknum=weeknum;
end;
run;
You are conditionally running the LAG() function call. Since you are calling it inside that IF first.month test it is calculating returning the first value of the previous month. Not the value of the previous record, which would be the last value of the previous month.
For now let's assume that is what you actually want to do.
It sounds like you just need to add more code to "forget' that lagged value when you are starting an new group.
So add a line to set DIFF missing when you are starting a new group.
data diff;
set have;
by brand state key descending month;
retain diff maxweeknum;
if first.month then do;
diff = ar-lag(ar);
if first.key then diff=.;
maxweeknum=weeknum;
end;
run;
That makes sense thank you. I ended up doing the following to accomodate for the other entries as well (Brand & State) which in my example works, but needs testing on the live data now.
Thanks!
data diff;
set have;
by brand state key descending month;
retain diff maxweeknum;
if first.month then do;
diff = ar-lag(ar);
if first.brand or first.State or first.Key then diff =.;
maxweeknum=weeknum;
end;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.