Loop to use business calendar to replace weekend/stats with next business calendar date

[Shannon Meadows]

I need to calculate the number of business days between two dates (variables:investigationstartdate and reporteddate) . I generated a business calendar, called bizcal.stbcal and saved to my ado folder on my C drive, to exclude weekends and Canadian Statutory holidays.

The issue I'm facing is that some of the dates in the source data (investigationstartdate and reporteddate) occur on weekends/holidays, so that when I use the following code, the respective business calendar dates for these variables (variable names with the suffix_biz) are missing.

I would like to generate a loop, so that if the business dates are missing, to add +1 days to the date in the source variables, and repeat until the respective business calendar date is not missing. (i.e., finding the next available date in the business calendar).

Here is the code that I used

bcal dir
bcal load bizcal

***2.A.4.0 Generate business days version of applicable date fields
gen investigationstartdate_biz=bofd("bizcal", investigationstartdate)
format %tbbizcal:CCYY-NN-DD investigationstartdate_biz

gen reporteddate_biz=bofd("bizcal", reporteddate)
format %tbbizcal:CCYY-NN-DD reporteddate_biz

***2.A.4.0.1 Dea


***2.A.4.0 Generate date difference from investigationstartdate & reporteddate from Business Cal
gen invest_report_diff=datediff(investigationstartdate_biz, reporteddate_biz, "day")

gen     problemid4 = 0
replace problemid4 = 1 if invest_report_diff>0

And here is an example of the data:

* Example generated by -dataex-. For more info, type help dataex
clear
input long investigationid float(reporteddate investigationstartdate investigationstartdate_biz reporteddate_biz invest_report_diff)
845218 19872 19507 105 366 261
395564 19726 19666 218 262  44
720675 19731 19725 261 265   4
395535 19725 19725 261 261   0
720429 19725 19725 261 261   0
395505 19725 19725 261 261   0
720319 19736 19726 262 268   6
395536 19726 19726 262 262   0
824026 19726 19726 262 262   0
395651 19726 19729 263 262  -1
720404 19729 19729 263 263   0
823128 19729 19729 263 263   0
720392 19729 19729 263 263   0
824031 19732 19730 264 266   2
720513 19730 19730 264 264   0
395695 19730 19730 264 264   0
720696 19730 19730 264 264   0
720451 19730 19730 264 264   0
824028 19730 19730 264 264   0
720531 19730 19730 264 264   0
395719 19730 19730 264 264   0
395722 19730 19730 264 264   0
720525 19730 19730 264 264   0
721161 19736 19731 265 268   3
824040 19731 19731 265 265   0
395775 19731 19731 265 265   0
720688 19731 19731 265 265   0
721337 19736 19731 265 268   3
824043 19731 19731 265 265   0
720641 19731 19731 265 265   0
824202 19732 19732 266 266   0
720864 19732 19732 266 266   0
720807 19732 19732 266 266   0
720874 19732 19732 266 266   0
720860 19732 19732 266 266   0
720793 19731 19732 266 265  -1
720792 19732 19732 266 266   0
395819 19732 19732 266 266   0
721709 19738 19732 266 270   4
720855 19732 19732 266 266   0
720841 19732 19732 266 266   0
823931 19732 19732 266 266   0
720870 19732 19732 266 266   0
825335 19733 19733 267 267   0
824195 19733 19733 267 267   0
824285 19732 19733 267 266  -1
824189 19731 19733 267 265  -2
720911 19733 19733 267 267   0
824248 19733 19733 267 267   0
824243 19733 19733 267 267   0
824739 19733 19733 267 267   0
721004 19733 19733 267 267   0
824187 19732 19733 267 266  -1
721565 19738 19733 267 270   3
824205 19733 19733 267 267   0
721216 19733 19736 268 267  -1
721157 19736 19736 268 268   0
721270 19736 19736 268 268   0
721738 19739 19736 268 271   3
824839 19736 19736 268 268   0
721189 19736 19736 268 268   0
395913 19736 19736 268 268   0
721172 19736 19736 268 268   0
721249 19736 19736 268 268   0
721197 19736 19736 268 268   0
824841 19736 19736 268 268   0
824637 19736 19736 268 268   0
721176 19736 19736 268 268   0
721171 19736 19736 268 268   0
721192 19736 19736 268 268   0
721217 19736 19736 268 268   0
722101 19740 19736 268 272   4
721568 19738 19737 269 270   1
721425 19737 19737 269 269   0
824930 19736 19737 269 268  -1
721442 19737 19737 269 269   0
825345 19737 19737 269 269   0
395971 19737 19737 269 269   0
721568 19738 19737 269 270   1
824979 19733 19737 269 267  -2
825163 19737 19737 269 269   0
825146 19737 19737 269 269   0
825339 19737 19737 269 269   0
721430 19737 19737 269 269   0
722096 19740 19737 269 272   3
825099 19737 19737 269 269   0
722322 19737 19737 269 269   0
396161 19739 19737 269 271   2
721977 19738 19737 269 270   1
722093 19740 19737 269 272   3
721572 19738 19738 270 270   0
825411 19738 19738 270 270   0
825385 19738 19738 270 270   0
826009 19738 19738 270 270   0
721563 19738 19738 270 270   0
721563 19738 19738 270 270   0
721571 19738 19738 270 270   0
396077 19738 19738 270 270   0
721601 19738 19738 270 270   0
721619 19738 19738 270 270   0
end
format %tdCCYY-NN-DD reporteddate
format %tdCCYY-NN-DD investigationstartdate
format %tbbizcal:CCYY-NN-DD investigationstartdate_biz
format %tbbizcal:CCYY-NN-DD reporteddate_biz

Let me know if anyone has thoughts on how to accomplish this.

[Clyde Schechter]

Replace

gen reporteddate_biz=bofd("bizcal", reporteddate)
format %tbbizcal:CCYY-NN-DD reporteddate_biz

with

assert !missing(reporteddate)
gen reporteddate_biz=bofd("bizcal", reporteddate)
count if missing(reporteddate_biz)
while r(N) > 0 {
    replace reporteddate = reporteddate+1
    replace reporteddate_biz=bofd("bizcal", reporteddate)
    count if missing(reporteddate_biz)
}
format %tbbizcal:CCYY-NN-DD reporteddate_biz

Note: This code is not tested, but I believe it is correct and free of typos. I did not test it because I do not have your business calendar to work with. Also, in the future, when asking for help with code, please chose a data example that exhibits the problem you are trying to solve. The example shown has no missing values of reporteddate_biz.

Added: The -assert- command at the beginning is crucial. If the data has a value of reporteddate that is missing, incrementing it by 1 will leave it unchanged, and Stata will hang in an infinite loop.

[Shannon Meadows]

.

[Shannon Meadows]

.

[Shannon Meadows]

Hello Clyde Schechter, thank you for your reply. I have included a subset of the data where reported date is present, and the corresponding business date of reported date was missing.
Using the code above, I am getting an infinite loop of replacing the reporteddate variable, despite including assert !missing(reporteddate) at the beginning. Any thoughts on how I could troubleshoot?

* Example generated by -dataex-. For more info, type help dataex
clear
input long investigationid float(reporteddate reporteddate_biz)
 830755 19770 .
 840191 19832 .
 841152 19833 .
 843882 19860 .
 845660 19874 .
 847415 19902 .
 847876 19910 .
 849154 19924 .
 850790 19924 .
 855516 19994 .
 856561 20001 .
 864082 20084 .
 867379 20077 .
 867965 20098 .
 869783 20106 .
 871523 20112 .
 877443 20141 .
 896396 20315 .
 897682 20329 .
 903946 20400 .
 907410 20435 .
 907616 20434 .
 908430 20448 .
 909495 20463 .
 925561 20547 .
 925649 20547 .
 925666 20546 .
 931770 20581 .
 931946 20588 .
 933839 20609 .
 940711 20687 .
 945246 20729 .
 949969 20784 .
 955681 20827 .
 959342 20840 .
 962255 20855 .
 968320 20890 .
 970315 20903 .
 971881 20911 .
 986892 21058 .
 995157 21141 .
1001875 21191 .
1001880 21191 .
1001890 21191 .
1001924 21190 .
1002817 21197 .
1013554 21232 .
1015461 21239 .
1015473 21239 .
1015644 21240 .
1015686 21239 .
1015709 21239 .
1015714 21240 .
1017401 21247 .
1017741 21246 .
1021050 21260 .
1021056 21260 .
1023937 21281 .
1025031 21289 .
1027737 21309 .
1032674 21358 .
1036209 21393 .
1044229 21471 .
1044671 21470 .
1044672 21470 .
1047236 21499 .
1050989 21533 .
1052759 21548 .
1061738 21589 .
1063077 21597 .
1065703 21611 .
1066360 21617 .
1067517 21624 .
1069761 21638 .
1070919 21646 .
1075846 21680 .
1077874 21702 .
1078563 21709 .
1080709 21729 .
1081282 21737 .
1081996 21743 .
1084988 21771 .
1085036 21772 .
1088255 21799 .
1094338 21862 .
1097256 21890 .
1103400 21918 .
1107225 21940 .
1108399 21939 .
1110943 21954 .
1111156 21953 .
1114096 21968 .
1114167 21967 .
1114176 21967 .
1115823 21975 .
1115864 21974 .
1115897 21974 .
1115949 21974 .
1116061 21974 .
1116084 21974 .
end
format %tdCCYY-NN-DD reporteddate

And here is the business calendar that I'm using. I'm using data 2024 onwards, and validating with some older data.

* Business calendar "bizcal" created by -bcal create-
* Created/replaced on 27 Jan 2025

version 16.1
dateformat ymd

range 2013jan02 2025dec31
centerdate 2013jan02

omit dayofweek (Sa Su)
omit date 20240101
omit date 20240329
omit date 20240401
omit date 20240520
omit date 20240701
omit date 20240805
omit date 20240902
omit date 20240930
omit date 20241014
omit date 20241111
omit date 20241225
omit date 20241226
omit date 20250101
omit date 20250217
omit date 20250418
omit date 20250519
omit date 20250701
omit date 20250901
omit date 20251013
omit date 20251225
omit date 20251226

[Clyde Schechter]

I do not get an infinite loop with the code. However, it does not run correctly. It produces incorrect results, and while I spot an error in the code, which I can fix, there is something else wrong which I have been unable to figure out. In particular, the initial command

gen reporteddate_biz=bofd("bizcal", reporteddate)

produces all missing values for reporteddate_biz. But with your example data, it should produce no missing values, all of your dates being within the range of your business calendar and none of them being designated as omitted. I don't understand why this is happening, and I don't know how to fix it.

Equally mysterious, when I run the loop, it iterates twice. On the first iteration, it seems to find 42 of your 100 example dates that it can now convert. But they are a seemingly arbitrary subset of the data. Then on the second iteration it is able to convert the rest of them. But, given that all of the values of reporteddate are within bizcal's range and are not omitted, there should never be any missing reporteddate_biz values from the start. So I am really confused about what is going on.

That said, I did find an error in my logic. The loop should not change the values of reporteddate. Rather it should create a copy of reporteddate that gets bumped up only if reporteddate_biz is still missing. So the "corrected" code is:

assert !missing(reporteddate)
format %tbbizcal:CCYY-NN-DD reporteddate_biz
gen tempdate = reporteddate if missing(reporteddate_biz)
format tempdate %td
count if missing(reporteddate_biz)
while r(N) > 0 {
    replace tempdate = tempdate + 1
    replace reporteddate_biz=bofd("bizcal", tempdate) if missing(reporteddate_biz)
    count if missing(reporteddate_biz)
}

But, as I said, this does not produce correct results and I cannot discern why.

Now, I do have a theory why you are getting an inifinite loop. If I try to run my code with a set of reporteddate values that just cover every date in year 2025, including 31 dec 2025, I get an infinite loop. That's because 31 dec 2025, which is the upper end of bizcal's range, is apparently treated as not a valid date. I guess the range limits are not "inclusive." So with that date being not within the bizcal range, when you increase it by 1 in each round of the loop, it only gets farther out of range! So see if your full data set includes a reporteddate's equal to or greater than 31 dec2025. If so, it isn't going to work, and what you probably need to do is re-generate bizcal setting its range farther into the future to cover all those dates.

Interestingly, if I run the "corrected" code on a made-up data set where reporteddate spans all of the dates from 1jan2024 to 30dec2025, the code runs, terminates, and produces correct results.

. clear*

. set obs `=365+365'
Number of observations (_N) was 0, now 730.

. gen reporteddate = td(31dec2023) + _n

.
. format %tdCCYY-NN-DD reporteddate

.
.
. assert !missing(reporteddate)

. bcal load bizcal
loading .\bizcal.stbcal ...

     1. * Business calendar "bizcal" created by -bcal create-
     2. * Created/replaced on 27 Jan 2025
     3.
     4. version 16.1
     5. dateformat ymd
     6.
     7. range 2013jan02 2025dec31
     8. centerdate 2013jan02
     9.
    10. omit dayofweek (Sa Su)
    11. omit date 20240101
    12. omit date 20240329
    13. omit date 20240401
    14. omit date 20240520
    15. omit date 20240701
    16. omit date 20240805
    17. omit date 20240902
    18. omit date 20240930
    19. omit date 20241014
    20. omit date 20241111
    21. omit date 20241225
    22. omit date 20241226
    23. omit date 20250101
    24. omit date 20250217
    25. omit date 20250418
    26. omit date 20250519
    27. omit date 20250701
    28. omit date 20250901
    29. omit date 20251013
    30. omit date 20251225
    31. omit date 20251226

(calendar loaded successfully)

. gen reporteddate_biz = .
(730 missing values generated)

. format %tbbizcal:CCYY-NN-DD reporteddate_biz

. gen tempdate = reporteddate

. format tempdate %td

. count if missing(reporteddate_biz)
  730

. while r(N) > 0 {
  2.     replace tempdate = tempdate + 1
  3.     replace reporteddate_biz=bofd("bizcal", tempdate) if missing(reporteddate_biz)
  4.     count if missing(reporteddate_biz)
  5. }
(730 real changes made)
(502 real changes made)
  228
(730 real changes made)
(107 real changes made)
  121
(730 real changes made)
(105 real changes made)
  16
(730 real changes made)
(14 real changes made)
  2
(730 real changes made)
(2 real changes made)
  0

.
end of do-file

So, those are the results of my experimentation. Maybe you can make it work from seeing this. If not, perhaps somebody else following a long can figure it out, because I'm just stumped why even a simple command like -gen reporteddate_biz=bofd("bizcal", reporteddate)- is producing incorrect results in your example data.

[Hemanshu Kumar]

All the dates in the example data are Saturdays and Sundays, and are thus omitted. Of these:

. count if dow(reporteddate) == 6
  58

. count if dow(reporteddate) == 0
  42

In the first iteration of the loop, the Sunday dates become Mondays, and thus get converted, while the Saturdays become Sundays and still can't be converted. It takes the second iteration to push Saturdays to convertible Mondays.

[Clyde Schechter]

Thank you, Hemanshu Kumar ! That explains much of what happened in #6. With that understood, it appears that my "corrected" code in #6 is actually corrected and should work for O.P., provided she either purges her data of dates that are out of the range of bizcal (noting that the range dates themselves are not included in the range) or modifies bizcal so that its range will include them.

[Shannon Meadows]

Thank you Clyde Schechter and Hemanshu Kumar. That update resolved the error. I did attempt to repeat the process with another date variable, investigationstartdate, but was getting an error stating that the assertion was false.

I updated my approach to add my datebiz variable to the a date calendar data asset dta file that contains all applicable dates for analysis (I used the updated code above for this). That way myself and my team could use this business date field without repeating the script above each time. Posting below, if anyone else has a similar problem. This worked well, thanks again for the assistance!

local date_vars reporteddate investigationstartdate

foreach var of local date_vars {
rename `var' date
merge m:1 date using "`date_dta'", keepusing(date datebiz)
drop if _merge==1 | _merge==2
drop _merge
rename date `var'
rename datebiz `var'_biz
}

[Clyde Schechter]

Glad it's working for you now. The code you show in #9 is fine. If desired, you can streamline it a little bit replacing

merge m:1 date using "`date_dta'", keepusing(date datebiz)
drop if _merge==1 | _merge==2
drop _merge

with the equivalent one-liner:

merge m:1 date using "`date_dta'", keepusing(date datebiz) keep(match) nogenerate

This will produce the same results. I doubt it will noticeably affect performance (speed), but the shorter code is, in this case, cleaner and more transparent.

[Shannon Meadows]

Always great to make things concise. Many thanks again!