complicated way to calculate monthly consumption

[tsai1108_gmail_com]

Hi, 

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.  

company	order date	volume
111	12/23/2016	4
111	1/4/2017	5
111	1/20/2017	3
123	11/22/2016	5
123	12/22/2016	3
123	2/25/2017	2

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!

Joanne

[Shmuel]

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 ?

[tsai1108_gmail_com]

yes, you are correct, it should be 4!

[Shmuel]

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;