Loops, Dates & Calculations

[BP Cooper]

Hello All,

I'm not sure if this has been posted before as I'm not quite sure how to search for it either way. I have a dataset of participants who receive case management. Case manager enter progress notes for each participant. I want to compute the avg number of progress notes across all active participants for each quarter. I can compute the number progress notes per quarter, not a problem. However, I have the enroll date and discharge date. I need to somehow tag each participant for each quarter of interest (as active) before I can include them in the average calculation for each quarter. I plan to use their enroll and discharge date, along with the known dates for each quarter to determine if they were active. I expect that I can use for loops, along with the tq function to do this but I can't figure out the right approach yet.

Thanks,
Ben

* Example generated by -dataex-. For more info, type help dataex
clear
input float(id dis_date initial_enroll_date)
779 22987 22858
137 22987 22922
441 22987 22950
901 23246 22847
117     . 22984
333     . 22977
692     . 23028
871     . 23028
977     . 23043
432 23246 23090
end
format %dM_d,_CY dis_date
format %dM_d,_CY initial_enroll_date

[Clyde Schechter]

Your question is a bit unclear. Looking at your example data, some of these enrollment and discharge date pairs span more than one quarter. Which quarter(s) do you want to count them in? The first, the last? All of the ones from the first to the last? Something else?

And what do you want to do with those where the discharge date is missing?

[Nick Cox]

SJ-13-1 dm0068 . . . . . Stata tip 114: Expand paired dates to pairs of dates
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . N. J. Cox
Q1/13 SJ 13(1):217--219 (no commands)
tip on using expand to deal with paired dates

may help. I am guessing a bit, but this code shows some technique.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(id dis_date initial_enroll_date)
779 22987 22858
137 22987 22922
441 22987 22950
901 23246 22847
117     . 22984
333     . 22977
692     . 23028
871     . 23028
977     . 23043
432 23246 23090
end
format %dM_d,_CY dis_date
format %dM_d,_CY initial_enroll_date

replace dis_date = mdy(1,24, 2024) if dis_date == . 

foreach v in dis_date initial_enroll_date { 
    gen q`v' = qofd(`v')
}

gen toexpand = qdis - qinit + 1 
expand toexpand 

bysort id (qdis) : gen qtr = qinit[1] + _n - 1 

bysort qtr (id) : gen wanted = _N 

list , sepby(qtr)

[BP Cooper]

Thanks Nick. I will look at your code. To answer Clyde's question, I was thinking of simply making a dummy variable for each of my quarters of interest. Then tag each person as 1/0 (active or not) for each dummy var. Then I can use that var in a loop to properly include active folks for each quarter that I loop through.

[Clyde Schechter]

Nick's approach in #3 will expand your existing data into a data set with one observation per id per quarter that the id appears in the dataset. This is a different way of doing it which may be more effective than the approach you propose in #4 for some things (probably most things), and less so for others. Since I don't know where you're going with this, I'll show you how to implement your approach. You then need to think about which way will work better for what you are specifically planning to do with this data.

assert initial_enroll_date <= dis_date
summ initial_enroll_date, meanonly
local first_quarter = qofd(`r(min)')
local last_quarter = qofd(td(24jan2024))

forvalues q = `first_quarter'/`last_quarter' {
    local qq: display %tq `q'
    gen byte active_`qq' = ///
        inrange(`q', qofd(initial_enroll_date), qofd(dis_date))
}

As with Nick's solution, missing values for discharge date are interpreted as meaning that as of today the person is still active.