Cutting off datastep calculations

[Krueger]

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.

[Tom · Accepted solution]

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;

[Krueger]

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;