Loop is not considering the condition

[sharath_rk]

Hello Team,

I am using the below loop to calculate the made & miss metrics however the loop is not considering the 2nd line to check for weekends/holidays. i am getting the results if i change the DATEPART(ORD_ENTRY_DATE) + 3 to DATEPART(ORD_ENTRY_DATE) + 1.

The calculation is if the delivery start date is within 3 business days of order entry date then it is made else it is a miss. Please advise.

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

ELSE DO;

  SELECT (REGION);

   WHEN ('Americas')

    DO;

     CHECK = DATEPART(ORD_ENTRY_DATE) + 3;

     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;

[Haikuo]

Have you considered using INTCK function with 'WEEKDAY' as interval?

Haikuo

[DBailey]

it would look like this:

if intck('WEEKDAY', datepart(ORD_ENTRY_DATE), datepart(DLVR_STRT_DTS))<=3 then C='MADE';

else C='MISS';

[sharath_rk]

Thank you very much. It worked . I wanted the calculation of weekdays to consider Amerhol ( American Holidays )  as well. Please advise how do i go about it.

[Kurt_Bremser]

How about doing it the other way around, by counting workdays?

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

ELSE DO;

  SELECT (REGION);

    WHEN ('Americas') DO;

      check = 0;

      do iter_date = DATEPART(ORD_ENTRY_DATE) to DATEPART(DLVR_STRT_DTS);

        if (WEEKDAY(iter_date) not IN (1,7) and INPUT(PUT(iter_date,AMERHOL.),9.) ne 1

        then check = check + 1; * count the actual number of workdays;

      end;

      IF check le 3

      THEN C = 'MADE';

      ELSE C = 'MISS';

    END;

[sharath_rk]

Thanks Kurt. It did not solve my problem. The below has solved my problem to a great extent and i just want to add AMERHOL to the below for the condition to consider weekday & no holiday for MADE. Please advise.

if intck('WEEKDAY', datepart(ORD_ENTRY_DATE), datepart(DLVR_STRT_DTS))<=3 then C='MADE';

else C='MISS';

[Patrick]

You could create a custom interval as described here: SAS(R) 9.3 Functions and CALL Routines: Reference

You could get the US holidays right for this custom interval using this code: 24655 - Using SAS to determine dates for U.S. holidays

And if you define these intervals as SAS datetime values then you wouldn't even need a datepart() function for your variables.

Most existing out-of-the box intervals have a version for SAS datetime values which starts with DT, eg. DTWEEKDAY

Untested:

if intck('DTWEEKDAY', ORD_ENTRY_DATE, DLVR_STRT_DTS)<=3 then C='MADE';

[Kurt_Bremser]

Could you provide some data for testing and the required results, please?

[sharath_rk]

Please advise how do i share the data file?

[Kurt_Bremser]

Just give us a data step with some example datalines and the desired results.

[sharath_rk]

REGION	OE_DATE	ORD_ENTRY_DATE	DLVR_STRT_DTS	DLVR_STRT_DATE	C
EMEA	3-Mar-14	03MAR2014:12:00:06.000000	07MAR2014:15:48:33.000000	7-Mar-14	MISS
EMEA	3-Mar-14	03MAR2014:13:10:28.000000	07MAR2014:10:29:35.000000	7-Mar-14	MISS
EMEA	3-Mar-14	03MAR2014:14:18:42.000000	07MAR2014:06:57:46.000000	7-Mar-14	MISS
EMEA	3-Mar-14	03MAR2014:14:58:54.000000	07MAR2014:07:37:04.000000	7-Mar-14	MISS
EMEA	3-Mar-14	03MAR2014:19:27:49.000000	07MAR2014:11:38:10.000000	7-Mar-14	MISS
EMEA	3-Mar-14	03MAR2014:09:46:08.000000	07MAR2014:00:07:29.000000	7-Mar-14	MISS

[sharath_rk]

proc format;

VALUE EMEAHOL  

                '04MAR2014'D,             

                '06MAR2014'D,

                '21MAR2014'D = 1

OTHER=0;

Run;

if intck('WEEKDAY', datepart(ORD_ENTRY_DATE), datepart(DLVR_STRT_DTS))<=3 then C='MADE';

else C='MISS';

In the above data set i should get column c as made as 3 business days from order entry date ( excludes order entry date ) should be 5th, 7th, 10th as 4th/6th are holidays & 8th/9th are weekends. Please advise.

[Patrick]

To write this once more: You can solve this using custom intervals as documented here: SAS(R) 9.3 Functions and CALL Routines: Reference

"Example 3: Using Custom Intervals with the INTCK Function

The following example uses custom intervals in the INTCK function to omit holidays when counting business days:"

Here some code variation of what has already been provided in the documentation. Hope that makes it clear for you.

data weekdays_2014;
  attrib
    begin format=date9.
  ;
  begin=intnx('weekday','01jan2014'd,0,'b');
  do while(year(begin)=2014);
    output;
    begin=intnx('weekday',begin,1,'b');
  end;
run;

data holidays_2014;
  infile datalines truncover;
  attrib
    region informat=$8.
    hol_date informat=date9. format=date9.;
  input region hol_date;
  datalines;
EMEA 01JAN2014
EMEA 04MAR2014
EMEA 06MAR2014
EMEA 21MAR2014
AMERHOL 01JAN2014
AMERHOL 20JAN2014
AMERHOL 17FEB2014
AMERHOL 09MAR2014
AMERHOL 26MAY2014
AMERHOL 04JUL2014
AMERHOL 01SEP2014
AMERHOL 13OCT2014
AMERHOL 02NOV2014
AMERHOL 04NOV2014
AMERHOL 11NOV2014
AMERHOL 27NOV2014
AMERHOL 25DEC2014
;
run;

proc sql;
  create table workdays_emea_2014 as
    select begin
    from weekdays_2014 a
      where begin not in (select hol_date from holidays_2014 where region='EMEA')
  ;
  create table workdays_amerhol_2014 as
    select begin
    from weekdays_2014 a
      where begin not in (select hol_date from holidays_2014 where region='AMERHOL')
  ;
quit;

options intervalds=(WD_AMERHOL=workdays_amerhol_2014 WD_EMEA=workdays_emea_2014);

data test;
  attrib
    order_dttm delivery_dttm format=datetime21.
  ;
  order_dttm='01jan2014:14:10:00'dt;
  delivery_dttm='01jan2014:08:35:00'dt;
  do _i=1 to 40;
    order_dttm=intnx('dtday',order_dttm,1,'b');
    delivery_dttm=intnx('dtday',delivery_dttm,4,'b');
    n_workdays_EMEA=intck('WD_EMEA',datepart(order_dttm),datepart(delivery_dttm));
    n_workdays_AMERHOL=intck('WD_AMERHOL',datepart(order_dttm),datepart(delivery_dttm));
    output;
  end;
run;

[sharath_rk]

Thank you Pat. I am sure the code would work but unfortunately i could not map it to my existing code. Please advise whether there's a shorter code.

[Reeza]

Build yourself a custom date dimension that has the date and then 3 business days prior. Then you can create  format or merge against the table to check your dates.

I doubt that's actually shorter, but probably simpler to understand.

Here's how I create my date dimension when I choose to go down that route.

This DOES NOT have the three day business day built in, but wouldn't be difficult to add.

SAS - Date Dimension