topic Re: calculate number of business days between 2 dates in SAS Programming

calculate number of business days between 2 dates

Ronein — Wed, 13 Jul 2022 06:02:22 GMT

Hello

There are 2 data sets:

date set "Dates" include dates that are business days in specific country.

Date set have includes 3 fields: customer_ID , date1, date2.

The target is to calculate how many business days between date1 and date2.

It means that function INTNX will not help becuase it can calculate number of days between date1 and date2 but I need to calculate number of business days between day2 and day2.

For example:

For ID=1 the difference should be 4 between 02OCT and 24SEP

'24SEP2020'd
'25SEP2020'd
'29SEP2020'd
'30SEP2020'd
'02OCT2020'd

For ID=2 the difference should be 1 between 09OCT and 08OCT

what is the way to calculate it please?


data dates;
format date date9.;
input date : date9.;
cards;
'01SEP2020'd
'02SEP2020'd
'03SEP2020'd
'04SEP2020'd
'06SEP2020'd
'07SEP2020'd
'08SEP2020'd
'09SEP2020'd
'10SEP2020'd
'11SEP2020'd
'13SEP2020'd
'14SEP2020'd
'15SEP2020'd
'16SEP2020'd
'17SEP2020'd
'18SEP2020'd
'21SEP2020'd
'22SEP2020'd
'23SEP2020'd
'24SEP2020'd
'25SEP2020'd
'29SEP2020'd
'30SEP2020'd
'02OCT2020'd
'04OCT2020'd
'05OCT2020'd
'06OCT2020'd
'07OCT2020'd
'08OCT2020'd
'09OCT2020'd
'11OCT2020'd
;
Run;


data have;
format date1 date2 date9.;
Input ID date1  : date9. date2  : date9.;
cards;
1 '02OCT2020'd '24SEP2020'd
2 '09OCT2020'd '08OCT2020'd
3 '13SEP2020'd '10SEP2020'd
;
Run;

Re: calculate number of business days between 2 dates

andreas_lds — Wed, 13 Jul 2022 06:13:10 GMT

Just an idea:

- use "dates" as hash object

- loop from date2 to date1

- increment a counter if hash.check() return 0

Re: calculate number of business days between 2 dates

andreas_lds — Wed, 13 Jul 2022 06:32:32 GMT

For ID=1 the difference should be 4 between 02OCT and 24SEP

'24SEP2020'd
'25SEP2020'd
'29SEP2020'd
'30SEP2020'd
'02OCT2020'd

Why 4?

Re: calculate number of business days between 2 dates

Kurt_Bremser — Wed, 13 Jul 2022 06:56:36 GMT

If you only want to exclude Saturdays and Sundays, look at the WEEKDAY interval for INTCK.

If you also need to exclude local holidays, set up a table of business days, load it into a hash, and loop over the whole time range and count the found business days.

Re: calculate number of business days between 2 dates

s_lassen — Wed, 13 Jul 2022 08:27:14 GMT

One way is to create a date "dimension", containing all the relevant dates (here shown with the dates in 2020):

data all_dates(index=(date));
  retain start_date '31dec2019'd;
  set dates end=done;
  where date between '01jan2020'd and '31dec2020'd; /* we are only creating for 2020 here */
  business_day=0;
  do date=start_date+1 to date-1;
    output;
    end;
  business_day=1;
  output;
  start_date=date;
  if done;
  business_day=0;
  do date=date+1 to '31dec2020'd;
    output;
    end;
  drop start_date;
run;

You can then use that to get the number of business days:

data want;
  set have;
  n_business_days=0;
  do date=date2 to date1;
    set all_dates key=date/unique;
    n_business_days+business_day;
    end;
  drop date business_day;
run;

If you do not want to count DATE1, even if it is a business day, just change the code to

data want;
  set have;
  n_business_days=0;
  do date=date2 to date1-1;
    set all_dates key=date/unique;
    n_business_days+business_day;
    end;
  drop date business_day;
run;

(which will give the result 4 for ID=1)

Re: calculate number of business days between 2 dates

FreelanceReinh — Wed, 13 Jul 2022 11:05:26 GMT

@s_lassen: I like the idea of an indexed lookup dataset because, once created, it can be used many times. Maybe for @Ronein's application it would be even more useful if it contained cumulative numbers of business days. The calculation of the number of business days between two dates would then reduce to a mere subtraction of two values retrieved from ALL_DATES instead of the "do date=date2 to date1;" loop with possibly many iterations and data retrievals. This would require only a few modifications of your code (highlighted below):

data all_dates(index=(date));
  retain start_date '31dec2019'd;
  set dates end=done;  where date between '01jan2020'd and '31dec2020'd; /* we are only creating for 2020 here */
  /* business_day=0; */
  do date=start_date+1 to date-1;
    output;
    end;
  business_day+1;
  output;
  start_date=date;
  if done;
  /* business_day=0; */
  do date=date+1 to '31dec2020'd;
    output;
    end;
  drop start_date;
run;

data want;
  set have;
  date=date1;
  set all_dates key=date/unique;
  _n_=business_day;
  date=date2;
  set all_dates key=date/unique;
  n_business_days=_n_-business_day;
  drop date business_day;
run;

The last data step yields the counts excluding DATE2, which meets the specifications.*

* Edit: Actually, the two examples given in the initial post would also be consistent with an exclusion of DATE1, but the code above could be adapted easily to that, e.g., by subtracting 1 from DATE1 and DATE2, resp., in the assignment statements for DATE in the second data step.

Re: calculate number of business days between 2 dates

ballardw — Wed, 13 Jul 2022 16:05:18 GMT

@Ronein wrote:

Hello

There are 2 data sets:

date set "Dates" include dates that are business days in specific country.

Date set have includes 3 fields: customer_ID , date1, date2.

The target is to calculate how many business days between date1 and date2.

It means that function INTNX will not help becuase it can calculate number of days between date1 and date2 but I need to calculate number of business days between day2 and day2.

Intnx never calculates "number of days" between anything. It increments values by a number of intervals. You mean to reference the INTCK function. If you have been using INTNX I would expect some very strange results indeed.

Re: calculate number of business days between 2 dates

s_lassen — Thu, 14 Jul 2022 11:00:45 GMT

I actually agree with you. But I think the right way is to go all the way "dimensional", and have both the number of business days (since some arbitrary start date) and the flag that indicates whether a specific date is a business day (and possibly the same markers for different countries), as a typical date dimension will never have more than a few thousand rows.

And the easy way to get the result may be to create a view, something like

proc sql;
  create view want as select have.*,
     (select n_business_day from dates where date=have.date1)-
     (select n_business_day from dates where date=have.date2) as n_business_days;
quit;

Of course you would then save both the underlying tables and the view on a permanent library or a server.

The reason I did it with the flag solution was just that it made for the simplest code initially.