@bssiders wrote:
I forgot one minor detail, smh. I need to account for holidays and weekends. In other words, I neeUsing a holiday table with company dates, I need to somehow pull that in.
So an account needs to appear 10+ business days in a row. Once it skips a business day, the count needs to start over. Each iteration of 10+ consec biz days per account needs to be output.
In my original code, instead of
do _ndays=1 by 1 until (last.id or _nxt_date> date+1);
I could change the _nxtdate>date+1 condition and equivalently use
do _ndays=1 by 1 until (last.id or _nxt_date> intnx('day',date,1);
to get exactly the same result.
But the real benefit of the intnx function in this case is that I can choose intervals other than ordinary calendar days (i.e. other than 'day'). Instead I can use a user-specified calendar, (let's call it "company_calendar") which can be set skip weekends and holidays (both general and company-specific holidays). Once that is done, as @Reeza suggests, it's pretty easy.
Let's say you make a calendar dataset work.mycomp_calendar . It has two date variables BEGIN and END (more about that later).
Then you could just do this:
options intervalds=(company_calendar=work.mycomp_calendar);
data want (drop=_:);
do _ndays=1 by 1 until (last.id or _nxt_date> intnx('company_calendar',date,+1));
set have (keep=id);
by id;
merge have
have (firstobs=2 keep=date rename=(date=_nxt_date));
end;
length newvar $13;
newvar=catx('-',id,_ndays);
do _n_=1 to _ndays;
set have;
if _ndays>=10 then output;
end;
run;
Edit: After getting actual sample data, I fixed syntax in the above code.
The OPTIONS statement tells sas to use the dataset work.mycomp_calendar for a calendar interval you choose to name "company_calendar".
As to the dataset work.mycomp_calendar, make a dataset with two variables (BEGIN and END) to cover the entire range of your data (see the Example 2: Custom Intervals section intnx function documentation to see what the dataset should look like.
Here's a trial company calendar dataset (your holidays may differ). It uses 2020 and 2021 as the calendar range:
data mycomp_calendar (keep=begin end);
format begin end weekdate22. ;
array hd_dummies{%sysevalf("01jan2020"d-7):%sysevalf("31dec2021"d+7)};
do year=year(lbound(hd_dummies)) to year(hbound(hd_dummies));
length hname $15;
do hname='NEWYEAR','MLK','USPRESIDENTS','MEMORIAL','USINDEPENDENCE','LABOR','THANKSGIVING','CHRISTMAS';
d=holiday(hname,year);
if lbound(hd_dummies) <= d <= hbound(hd_dummies) then hd_dummies{d}=1;
end;
end;
do begin=intnx('weekday','01jan2020'd,0) by 0 until (end>='31dec2021'd);
do nxtbeg=intnx('weekday',begin,1) by 0 while(hd_dummies(nxtbeg)=1);
nxtbeg=intnx('weekday',nxtbeg,1);
end;
end=nxtbeg-1;
output;
begin=nxtbeg; **Ready for next loop iteration **;
end;
run;
Notice the holiday dummies array hd_dummies I extend the date range by 7 days at each end of the array. This assures that the INTNX function used to build the company calendar does not go beyond the array range (i.e. when calculating END for the last interval and BEGIN for the first interval).
