topic Difference between two work day dates dates in SAS Programming

Difference between two work day dates dates

RandyStan — Mon, 02 Mar 2020 04:52:08 GMT

My work date calendar is as follows

05AUG2019

06AUG2019

07AUG2019

08AUG2019

09AUG2019

13AUG2019

14AUG2019

16AUG2019

19AUG2019

I want to find the difference between dates for the following data

ID_A ID_B Date

X A 05AUG2019

X A 07AUG2019

X A 13AUG2019

Y A 13AUG2019

Y A 16AUG2019

Y A 19AUG2019

M C 14AUG2019

The data I want is

ID_A ID_B Date DBT

X A 05AUG2019 0

X A 07AUG2019 2

X A 13AUG2019 3

Y A 13AUG2019 0

Y A 16AUG2019 2

Y A 19AUG2019 1

M C 14AUG2019 0

Please help

Thanx in advance

Randy

Re: Difference between two work day dates dates

Patrick — Mon, 02 Mar 2020 05:10:41 GMT

You can find a lot of pointers for what you need in SAS Tip: Generating Holiday Lists

Does this suffice or do you need more guidance?

Re: Difference between two work day dates dates

RandyStan — Mon, 02 Mar 2020 05:26:12 GMT

Thanks Patrick. But it will not suffice. It is a 10 year data set with varying holidays. one option is to construct the workday calendar and use the options

options intervalds = (Begin = workingdays);

But for some reason the use of intervalds does not work.

Randy

Re: Difference between two work day dates dates

fdsaaaa — Mon, 02 Mar 2020 06:59:02 GMT

If you have your calendar already in a dataset , you can add a counter

WORKING_DAY WORKING_DAY_CNT

05AUG2019 1

06AUG2019 2

07AUG2019 3

08AUG2019 4

09AUG2019 5

13AUG2019 6

14AUG2019 7

16AUG2019 8

19AUG2019 9

Merge the working_day_cnt onto your "ID" dataset by the date

ID_A ID_B Date WORKING_DAY_CNT

X A 05AUG2019 1

X A 07AUG2019 3

X A 13AUG2019 6

Y A 13AUG2019 6

Y A 16AUG2019 8

Y A 19AUG2019 9

M C 14AUG2019 7

Then use first. processing and a retain statement to calculate your DBT

Re: Difference between two work day dates dates

mkeintz — Mon, 02 Mar 2020 07:28:03 GMT

Initialize a temporary array from calendar-beginning to calendar-end with a single one followed by all zeroes. Just set up the array such that the first date is not a holiday.
Read the complete workday calendar dates (say in data set WDAYS with one variable DATE) and change the corresponding array element to 1's.
Loop through the array, starting at the second position. Revise each element by adding its current value (0 for holidays, 1 for workday) to the value in the preceding element.
You now have an array, indexed by date, with values showing the relative workday position of that date. Holidays will have the value as the nearest preceding workday.
Now read data set have and compare DVALS of current and preceding date (except for first.id_a=1):

%let calbeg=20dec2002;
%let calend=31dec2013;
%let nzeroes=%eval(%sysevalf("&calend"d)-%sysevalf("&calbeg"d));
%put _user_;

data want (drop=_:);
  array dvals {%sysevalf("&calbeg"d):%sysevalf("&calend"d)} _temporary_ (1 &nzeroes*0);
  if _n_=1 then do;
    do until (end_of_wdays);
      set wdays end=end_of_wdays;
      dvals{date}=1;
    end;
    do _d=lbound(dvals)+1 to hbound(dvals);    
      dvals{_d}=dvals{_d-1}+dvals{_d};
    end;
  end;
  set have;
  by id_a;
   DBT=dvals{date} - ifn(first.id_a,dvals{date},dvals{lag(date)});
run;

Note the second do loop could also be specified as:

  do _d="&calbeg"d+1 to "&calend"d;

Re: Difference between two work day dates dates

Patrick — Mon, 02 Mar 2020 11:23:44 GMT

@RandyStan wrote:

Thanks Patrick. But it will not suffice. It is a 10 year data set with varying holidays. one option is to construct the workday calendar and use the options

options intervalds = (Begin = workingdays);

But for some reason the use of intervalds does not work.

Randy

Below code uses your sample data with intervalds. It's pretty much a simplified version of what's in the link I've posted earlier.

data active_days;
  input begin:date9.;
  format begin date9.;
  datalines;
05AUG2019
06AUG2019
07AUG2019
08AUG2019
09AUG2019
13AUG2019
14AUG2019
16AUG2019
19AUG2019
;

options intervalds=(BusinessDay=active_days) ;

data have;
  input (ID_A ID_B) ($) have_dt:date9. diff_expected;
  format have_dt date9.;
  datalines;
X A 05AUG2019 0
X A 07AUG2019 2
X A 13AUG2019 3
Y A 13AUG2019 0
Y A 16AUG2019 2
Y A 19AUG2019 1
M C 14AUG2019 0
;

proc sort data=have;
  by id_a id_b have_dt;
run;

data want(drop=_:);
  set have;
  by id_a id_b have_dt;
  _lag_dt=lag(have_dt);
  if first.id_B then _lag_dt=have_dt;
  diff_calculated=intck('BusinessDay',_lag_dt,have_dt);
run; 

proc print data=want;
run;

Re: Difference between two work day dates dates

yabwon — Mon, 02 Mar 2020 12:34:56 GMT

Hi,

Among all nice, already provided, solutions the following also do the job:

data wday;
input date date9.;
format date date9.;
cards4;
05AUG2019
06AUG2019
07AUG2019
08AUG2019
09AUG2019
13AUG2019
14AUG2019
16AUG2019
19AUG2019
;;;;
run;
proc transpose 
  data = wday 
  out = wday (drop=_name_)
  prefix=wd;
  var date;
run;

data have;
input ID_A : $ ID_B : $ date date9.;
format date date9.;
cards4;
X A 05AUG2019
X A 07AUG2019
X A 13AUG2019
Y A 13AUG2019
Y A 16AUG2019
Y A 19AUG2019
M C 14AUG2019
;;;;
run;
proc print;
run;


%let start=05AUG2019;
%let end  =19AUG2019;
data want;
  array D[%sysevalf("&start."d):%sysevalf("&end."d)] _temporary_;
  sentinelA="A"; 
    set wday; 
  sentinelB="B";
  array wdays sentinelA-numeric-sentinelB;
  do over wdays;
    D[wdays] = 1; 
  end;

  do until (eof);
    set have end=eof;
    by ID_A notsorted; 
      lag_date = ifn(first.ID_A, ., lag(date));
      diff = -1;
      if date > lag_date > .z then 
        do _N_ = lag_date to date; 
          diff + d[_N_]; 
        end;
      else diff = 0; 
    output;
  end;

  stop;
  drop wd: sentinel: lag_date; 
run;
proc print;
run;

All the best

Bart