topic Re: Loop is not considering the condition in SAS Enterprise Guide

Loop is not considering the condition

sharath_rk — Fri, 07 Mar 2014 14:15:58 GMT

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;

Re: Loop is not considering the condition

Haikuo — Fri, 07 Mar 2014 14:37:10 GMT

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

Haikuo

Re: Loop is not considering the condition

DBailey — Fri, 07 Mar 2014 14:52:43 GMT

it would look like this:

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

else C='MISS';

Re: Loop is not considering the condition

sharath_rk — Tue, 11 Mar 2014 08:54:22 GMT

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.

Re: Loop is not considering the condition

Kurt_Bremser — Tue, 11 Mar 2014 11:47:23 GMT

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;

Re: Loop is not considering the condition

sharath_rk — Fri, 14 Mar 2014 10:58:13 GMT

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

Re: Loop is not considering the condition

sharath_rk — Fri, 14 Mar 2014 11:08:34 GMT

Hai,

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

Re: Loop is not considering the condition

Patrick — Fri, 14 Mar 2014 11:38:28 GMT

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

Re: Loop is not considering the condition

Kurt_Bremser — Fri, 14 Mar 2014 12:11:06 GMT

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

Re: Loop is not considering the condition

sharath_rk — Fri, 14 Mar 2014 12:30:56 GMT

Please advise how do i share the data file?

Re: Loop is not considering the condition

Kurt_Bremser — Fri, 14 Mar 2014 12:32:54 GMT

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

Re: Loop is not considering the condition

sharath_rk — Fri, 14 Mar 2014 13:19:07 GMT

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

Re: Loop is not considering the condition

sharath_rk — Fri, 14 Mar 2014 13:30:04 GMT

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.

Re: Loop is not considering the condition

Patrick — Sat, 15 Mar 2014 06:55:56 GMT

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;

Re: Loop is not considering the condition

sharath_rk — Tue, 18 Mar 2014 14:23:48 GMT

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.

Re: Loop is not considering the condition

Reeza — Tue, 18 Mar 2014 15:19:43 GMT

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

Re: Loop is not considering the condition

Reeza — Tue, 18 Mar 2014 15:42:04 GMT

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.

Re: Loop is not considering the condition

Patrick — Wed, 19 Mar 2014 03:11:43 GMT

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.

Re: Loop is not considering the condition

sharath_rk — Wed, 19 Mar 2014 12:53:24 GMT

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

Re: Loop is not considering the condition

sharath_rk — Wed, 19 Mar 2014 13:01:32 GMT

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 )