DATA Step, Macro, Functions and more

complicated way to calculate monthly consumption

New Contributor
Posts: 3

complicated way to calculate monthly consumption


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 


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!



Trusted Advisor
Posts: 1,831

Re: complicated way to calculate monthly consumption

Posted in reply to tsai1108_gmail_com

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 ?


New Contributor
Posts: 3

Re: complicated way to calculate monthly consumption

yes, you are correct, it should be 4!
Trusted Advisor
Posts: 1,831

Re: complicated way to calculate monthly consumption

[ Edited ]
Posted in reply to tsai1108_gmail_com

Attached code is not final but it may give a base to accomplish it

data have;
   input company date mmddyy10. volume;
   format date date9.;
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 then upto_date=.; 
     upto_date = date;
     format upto_date date9.;
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;
       days_consumed_in_month = last_date_of_month - date;

    if not 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);


Ask a Question
Discussion stats
  • 3 replies
  • 2 in conversation