Loop is not considering the condition

[Reeza]

Build a custom date lookup table that has a date and the expected shipping date.

Use a look up to check your status.

I don't know if its any faster, but its easier to implement and understand.

[Patrick]

The main work is to build up some sort of working day custom calendars per country/region. Once you're there you can use any of the already proposed approaches.

I myself would still go for the "custom intervals" approach as this will allow you to use SAS calendar functions.

[sharath_rk]

Pat,

In your below code will n_workdays_EMEA give three business days interval as output and how do i get the intervals > 3 business days. Please advise. I am still trying to digest your code.

n_workdays_EMEA=intck('WD_EMEA',datepart(order_dttm),datepart(delivery_dttm));

n_workdays_AMERHOL=intck('WD_AMERHOL',datepart(order_dttm),datepart(delivery_dttm));

[Patrick]

In the code I've previously posted replace the "data test" step with below code. Hope that will explain it to you.

data test2;
  infile datalines truncover;
    attrib
    REGION informat=$8.
    ORD_ENTRY_DTS DLVR_STRT_DTS informat=datetime21. format=datetime21.
    n_workdays length=8
    _interval length=$20.
    c length=$4.
    ;
  input REGION ORD_ENTRY_DTS DLVR_STRT_DTS;

  length _interval $20.;
  select(REGION);
    when('EMEA')     _interval='WD_EMEA';
    when('AMERHOL')  _interval='WD_AMERHOL';
    otherwise        _interval='WEEKDAY';
  end;
 
  n_workdays=intck(_interval,datepart(ORD_ENTRY_DTS),datepart(DLVR_STRT_DTS));
  if n_workdays>3 then c='MISS';
  else c='MET';

datalines;
EMEA 25FEB2014:12:00:06.000000 28FEB2014:12:00:06.000000
EMEA 28FEB2014:12:00:06.000000 07MAR2014:15:48:33.000000
EMEA 28JAN2014:12:00:06.000000 07FEB2014:15:48:33.000000
AMERHOL 25FEB2014:12:00:06.000000 28FEB2014:12:00:06.000000
AMERHOL 28FEB2014:12:00:06.000000 07MAR2014:15:48:33.000000
AMERHOL 28JAN2014:12:00:06.000000 07FEB2014:15:48:33.000000
;
run;

And how this works is that the intck() function will use your start and end date and look up in the custom interval as passed to the function (eg: EMEA) how many intervals it matches. So there it's simply counting working days between the start and end date.

[sharath_rk]

Thank you Pat. I will try & get back to you tomorrow.

[sharath_rk]

Pat,

I have created a custom table per below using your previous code however i need the condition to check for country name & holiday date in EMEA region to proceed further. Please advise.

EMEA_holidays_2014;

  infile datalines truncover;

  attrib

    region informat=$5.

country informat=$25.

    hol_date informat=date9. format=date9.;

  input region Country hol_date;

    datalines;

EMEA      Russia                  10MAR2014

EMEA      Ukraine                 10MAR2014

EMEA      South_Africa          21MAR2014

EMEA      Greece                  25MAR2014

EMEA      South_Africa          28MAR2014

EMEA      Ireland                   17APR2014

;

Run;

Requirement 1}

SELECT (REGION);

WHEN (EMEA)

DO;

IF WEEKDAY(DATEPART(ORD_ENTRY_DATE)) NOT IN (1,7) AND DATEPART(ORD_ENTRY_DATE) NOT IN EMEA_holidays_2014 THEN DO C='Made';

ELSE C='Miss';

Requirement 2}

SELECT (REGION);

WHEN (EMEA)

DO;

CHECK = DATEPART(ORD_ENTRY_DATE) + 3;

DO WHILE (WEEKDAY(CHECK) IN (1,7) OR OR INPUT(PUT(CHECK,EMEA_holidays_2014.),6.);

CHECK = CHECK + 1;

IF CHECK >= DATEPART(DLVR_STRT_DTS)   THEN C='Made';

ELSE C='Miss';

[Patrick]

The approach remains: You need to create a separate calendar (table) per area with common working days and then use each of these tables to create a custom interval. So if your areas are countries and not regions then you need to do this by country. The logic I've posted already remains the same.

By the way: In some countries holidays differ also by state. Depending on how precise you need to be the main work will be to collect all this information as I'm not aware of a "central register" for it.

[sharath_rk]

Pat,

Thank you. how can i call the variable EMEA_holidays_2014 in my calculation where the lookup need to consider weekends/AMERHOL/EMEA holidays and if order entry date is among any of these then c='miss';

Please advise.

[Patrick]

Instead of creating holiday calendars per region you will need to create holiday calendars per country and then based on this data custom intervals per country. Else everything works the same as in the code I've already posted. It's only about applying the logic per country instead of per region.

I am not proposing to create an EMEA_holidays_2014 variable and you would need some looping mechanism for such an approach. I believe the custom interval approach is what you should do - but as said earlier creating all these calendars will be a bit of work.

[sharath_rk]

Thank you Pat. I will try custom interval approach as advised.

[JB1_DK]

The best approach is to use your holiday calendar data as source for a hash object with keys Region and Country. Using the Check function, you can lookup and test whether or not you get a hit.

Look at Example 2 on this page: http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#n1b4cbtmb049xtn1vh9x...

-JB

[sharath_rk]

Reeza,

Would you please advise how to build a custom data look up table, i need a column which says Y if the order entry date + 3 business days is > = delivery start date

else the value should be N.

I have created a custom table however it is not excluding the weekend. for example order entry date 11th mar 2014 and the Y orders have delivery start date from 12th march till 16th march ( 12,13,14 - business days & 15,16 are weekends which are non business & should have been excluded )

[Reeza]

Post what you've tried then. 

[sharath_rk]

IF DLVR_STRT_DTS = . THEN C = 'N/A';

ELSE DO;

  SELECT (REGION);

   WHEN ('Americas')

    DO;

     CHECK = DATEPART(ORD_ENTRY_DATE) + 1;

     DO WHILE (WEEKDAY(CHECK) IN (1,7) OR INPUT(PUT(CHECK,AMERHOL.),9.) = 1);

      CHECK = CHECK + 1;

     END;

     IF CHECK >= DATEPART(DLVR_STRT_DTS)  THEN C = 'MADE';

     ELSE                                                                       C = 'MISS';

    END;

The above code works only for 1 business day interval. If i change the number 1 to 3 and run the code again then the DO WHILE condition is ignored. Please advise.

[sharath_rk]

I have also tried the below however it is not excluding the weekend. For example : order entry date 11th mar 2014 and the Made orders have delivery start date from 12th march till 16th march ( 12,13,14 - business days & 15,16 are weekends which are non business & should have been excluded )

IF DLVR_STRT_DTS = .                                                                                                         THEN CFI_PERFORMANCE = 'N/A';

ELSE IF intck('WEEKDAY',datepart(ORD_ENTRY_DATE),datepart(DLVR_STRT_DTS)) <=3        THEN CFI_PERFORMANCE = 'MADE';

ELSE                                                                                                                                    CFI_PERFORMANCE = 'MISS';