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
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?
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
@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;
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
%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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.