New Contributor
Posts: 3

# complicated way to calculate monthly consumption

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

Posts: 1,831

## Re: complicated way to calculate monthly consumption

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!
Posts: 1,831

## Re: complicated way to calculate monthly consumption

[ Edited ]

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;
``````

Discussion stats
• 3 replies
• 123 views
• 0 likes
• 2 in conversation