BookmarkSubscribeRSS Feed
Hessam
Fluorite | Level 6
 
 
I have the LastOrderChance date meaning the latest time a product should be ordered to avoid shortage in that warehouse. My challenge is how do i group the products which needs to be ordered into the closest order cycle (the time as per the regular ordering points) so the shortages are added into those order cycles. 
 
For example if a product must be ordered by Oct 2018 and the order cycle for that region / warehouse is quarterly (Jan, Mar, Sep, Dec), the qty and product should be added to the September order. 
The closest or better to say the right order cycle should be shown in a new column for each row. 
 
 
Min_Inventory_Count Table
  • RegionID; region indicator 
  • MonthYear; the month and year for the record
  • ProductCode; the product which the record is about
  • ShortageQty,  the number of units in shortage from that product in the month and year (MonthYear column)
  • LastChancetoOrder;  this is a calculated date backward of the MonthYear minus the lead time in months. This columns tells which date at latest the product must be ordered given the delivery lead time so qty in shortage is arrived in the current MonthYear
  • ReOrderStartDate;  this columns is brought from the RegionDate table and is the date of the first order in the year for that warehouse/region.
  • MonthsBetweenOrders; the number of months interval between the first order and second (following) orders
  • NoofOrdersPerYear; also brought from the other table, indicates the number of annual orders for each warehouse/region
  • DeliveryLeadTime; brought from the other table, shows the delivery lead time for the product to that warehouse

 

 

RegionIDMonthYearProductCodeShortageQtyLastChancetoOrderOrderFirstDateMonthsBetweenOrdersNoofOrdersPerYearDeliveryLeadTime
111/1/2018FA-520I3110/1/201715-Sep263
112/1/2018FA-465C611/1/201715-Sep263
112/1/2018FA-520I611/1/201715-Sep263
223/1/2018FA-465C612/1/201715-Sep343
223/1/2018FA-696312/1/201715-Sep343
224/1/2018FA-465C52/1/201815-Sep343
6 REPLIES 6
TomKari
Onyx | Level 15

It looks like we know the date that a product must be ordered by (LastOrderChance), but how do we know the "order cycle for that region / warehouse is quarterly (Jan, Mar, Sep, Dec)"?

Hessam
Fluorite | Level 6

Hi Tom

 

The cycles are  'OrderFirstDate' column date + MonthsBetweenOrders

 

Regards

Hessam  

TomKari
Onyx | Level 15

Taking the first line, if I understand your requirements you want to order ProductCode FA-5201 on or before Oct 1, 2017. But your "OrderFirstDate" is Sep 15, 2018. So there's no way to add "MonthsBetweenOrders" to that to come to less than "LastOrderChance".

Hessam
Fluorite | Level 6

I corrected the dataset actually the first order should refer to the month and day, any year. 

 

 

MarkWik
Quartz | Level 8

@Hessam You could show a sample of your expected OUTPUT explaining a the logic referring to it. Otherwise, it's gonna go back and forth

Hessam
Fluorite | Level 6

Given the dataset, each region's reorder points bucket (based on month) is:

 

BucketRegion 22Region 11
115-Sep15-Sep
215-Jan15-Nov
315-Mar15-Jan
415-Jun15-Mar
5 15-May
6 15-Jul

 

Expectation is to assign those products and their quantities (whose LastChancetoOrder date falls between any bucket) to the closest bucket before LastChancetoOrder 

 

 The result should look like below (last column ReorderBucket)

 

RegionIDMonthYearProductCodeShortageQtyLastChancetoOrderOrderFirstDateMonthsBetweenOrdersNoofOrdersPerYearDeliveryLeadTimeReorderBucket
111/1/2018FA-520I3110/1/201715-Sep263 15-Sep-2017
112/1/2018FA-465C611/1/201715-Sep263 15-Sep-2017
112/1/2018FA-520I611/1/201715-Sep263 15-Sep-2017
223/1/2018FA-465C612/1/201715-Sep343 15-Sep-2017
223/1/2018FA-696312/1/201715-Sep343 15-Sep-2017
224/1/2018FA-465C52/1/201815-Sep343 15-Jan-2018

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 936 views
  • 0 likes
  • 3 in conversation