02-18-2017 12:31 PM
I was given an interesting task with somehow complicated logic to caluclate monthly consumption. I was given three variables as such, and I have certain logic,
look for the next order date, and if volume is bigger than the previous order, if volume is bigger, assuming the customer has used up all the inventory. if the next order is smaller, then assuming customer has only used up the new order amount (for replenshiment).
Then I need to count the daily consumption volume based on the volume and number of days between order. Then I calculate number of days used in any given month to come up with a monthly consumption number for any given customer. My final goal is to sum up by month, so I can get a consumption total by month.
outcome (or my way of thinking) is attached.
I have tried several different ways, but can't quite figure it out. can someone help, Is there any easier way to do this?
Thanks so much!
02-18-2017 01:43 PM
I would like to clarify a point, relating just to first two rows:
if volume on 23DEC2016 was 4 and on 04JAN 2017 there is an order of 5
isn't consumption = 4, the previous volume ?
02-18-2017 03:15 PM - edited 02-18-2017 03:39 PM
Attached code is not final but it may give a base to accomplish it
data have; input company date mmddyy10. volume; format date date9.; datalines; 111 12/23/2016 4 111 01/04/2017 5 111 01/20/2017 3 123 11/22/2016 5 123 12/22/2016 3 123 02/25/2017 2 ; run; proc sort data=have; by company descending date; run; data temp; set have; by company; retain upto_date; if first.company then upto_date=.; output; upto_date = date; format upto_date date9.; run; proc sort data=temp; by company date; run; data want; set temp; by company; lag_date = lag(date); /* previous date */ lag_vol = lag(volume); first_date_of_month = intnx('month',date,0,'B'); last_date_of_month = intnx('month',date,0,'E'); format first_date_of_month last_date_of_month lag_date date9.; days_between = upto_date - date; month = month(date); if put(upto_date, yymmn6.) = put(date, yymmn6.) then days_consumed_in_month = days_between; else days_consumed_in_month = last_date_of_month - date; if not first.company then do; days_between = date - lag_date; if volume > lag_vol then consumption = lag_vol; else consumption = lag_vol - volume; daily_consumption = round(consumption / days_between, 0.01); days_consumed_in_month = min(date,last_date_of_month) - max(lag_date, first_date_of_month); end; run;