BookmarkSubscribeRSS Feed
tsai1108_gmail_com
Calcite | Level 5

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 
11112/23/20164
1111/4/20175
1111/20/20173
12311/22/20165
12312/22/20163
1232/25/20172

 

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

3 REPLIES 3
Shmuel
Garnet | Level 18

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
Calcite | Level 5
yes, you are correct, it should be 4!
Shmuel
Garnet | Level 18

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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