topic Re: Finding a date closest to a date from range in SAS Enterprise Guide

Finding a date closest to a date from range

Hessam — Sat, 19 May 2018 16:33:12 GMT

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

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

Re: Finding a date closest to a date from range

TomKari — Sat, 19 May 2018 14:50:42 GMT

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)"?

Re: Finding a date closest to a date from range

Hessam — Sat, 19 May 2018 15:02:48 GMT

Hi Tom

The cycles are 'OrderFirstDate' column date + MonthsBetweenOrders

Regards

Hessam

Re: Finding a date closest to a date from range

TomKari — Sat, 19 May 2018 15:30:18 GMT

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".

Re: Finding a date closest to a date from range

Hessam — Sat, 19 May 2018 15:53:28 GMT

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

Re: Finding a date closest to a date from range

MarkWik — Sat, 19 May 2018 15:55:40 GMT

@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

Re: Finding a date closest to a date from range

Hessam — Sat, 19 May 2018 16:32:53 GMT

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