Desktop productivity for business analysts and programmers

Loop is not considering the condition

Reply
Contributor
Posts: 51

Loop is not considering the condition

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;

Respected Advisor
Posts: 3,124

Re: Loop is not considering the condition

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

Haikuo

Super Contributor
Posts: 578

Re: Loop is not considering the condition

it would look like this:

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

else C='MISS';

Contributor
Posts: 51

Re: Loop is not considering the condition

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

Esteemed Advisor
Posts: 6,726

Re: Loop is not considering the condition

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 51

Re: Loop is not considering the condition

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';

Respected Advisor
Posts: 3,841

Re: Loop is not considering the condition

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';

Esteemed Advisor
Posts: 6,726

Re: Loop is not considering the condition

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 51

Re: Loop is not considering the condition


Please advise how do i share the data file?

Esteemed Advisor
Posts: 6,726

Re: Loop is not considering the condition

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 51

Re: Loop is not considering the condition

REGIONOE_DATEORD_ENTRY_DATEDLVR_STRT_DTSDLVR_STRT_DATEC
EMEA3-Mar-1403MAR2014:12:00:06.00000007MAR2014:15:48:33.0000007-Mar-14MISS
EMEA3-Mar-1403MAR2014:13:10:28.00000007MAR2014:10:29:35.0000007-Mar-14MISS
EMEA3-Mar-1403MAR2014:14:18:42.00000007MAR2014:06:57:46.0000007-Mar-14MISS
EMEA3-Mar-1403MAR2014:14:58:54.00000007MAR2014:07:37:04.0000007-Mar-14MISS
EMEA3-Mar-1403MAR2014:19:27:49.00000007MAR2014:11:38:10.0000007-Mar-14MISS
EMEA3-Mar-1403MAR2014:09:46:08.00000007MAR2014:00:07:29.0000007-Mar-14MISS
Contributor
Posts: 51

Re: Loop is not considering the condition

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.

Respected Advisor
Posts: 3,841

Re: Loop is not considering the condition

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;

Contributor
Posts: 51

Re: Loop is not considering the condition

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.

Grand Advisor
Posts: 17,464

Re: Loop is not considering the condition

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

Ask a Question
Discussion stats
  • 30 replies
  • 1169 views
  • 12 likes
  • 7 in conversation