RegionID | MonthYear | ProductCode | ShortageQty | LastChancetoOrder | OrderFirstDate | MonthsBetweenOrders | NoofOrdersPerYear | DeliveryLeadTime |
11 | 1/1/2018 | FA-520I | 31 | 10/1/2017 | 15-Sep | 2 | 6 | 3 |
11 | 2/1/2018 | FA-465C | 6 | 11/1/2017 | 15-Sep | 2 | 6 | 3 |
11 | 2/1/2018 | FA-520I | 6 | 11/1/2017 | 15-Sep | 2 | 6 | 3 |
22 | 3/1/2018 | FA-465C | 6 | 12/1/2017 | 15-Sep | 3 | 4 | 3 |
22 | 3/1/2018 | FA-696 | 3 | 12/1/2017 | 15-Sep | 3 | 4 | 3 |
22 | 4/1/2018 | FA-465C | 5 | 2/1/2018 | 15-Sep | 3 | 4 | 3 |
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)"?
Hi Tom
The cycles are 'OrderFirstDate' column date + MonthsBetweenOrders
Regards
Hessam
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".
I corrected the dataset actually the first order should refer to the month and day, any year.
@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
Given the dataset, each region's reorder points bucket (based on month) is:
Bucket | Region 22 | Region 11 |
1 | 15-Sep | 15-Sep |
2 | 15-Jan | 15-Nov |
3 | 15-Mar | 15-Jan |
4 | 15-Jun | 15-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)
RegionID | MonthYear | ProductCode | ShortageQty | LastChancetoOrder | OrderFirstDate | MonthsBetweenOrders | NoofOrdersPerYear | DeliveryLeadTime | ReorderBucket |
11 | 1/1/2018 | FA-520I | 31 | 10/1/2017 | 15-Sep | 2 | 6 | 3 | 15-Sep-2017 |
11 | 2/1/2018 | FA-465C | 6 | 11/1/2017 | 15-Sep | 2 | 6 | 3 | 15-Sep-2017 |
11 | 2/1/2018 | FA-520I | 6 | 11/1/2017 | 15-Sep | 2 | 6 | 3 | 15-Sep-2017 |
22 | 3/1/2018 | FA-465C | 6 | 12/1/2017 | 15-Sep | 3 | 4 | 3 | 15-Sep-2017 |
22 | 3/1/2018 | FA-696 | 3 | 12/1/2017 | 15-Sep | 3 | 4 | 3 | 15-Sep-2017 |
22 | 4/1/2018 | FA-465C | 5 | 2/1/2018 | 15-Sep | 3 | 4 | 3 | 15-Jan-2018 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.