Generating a count variable of inpatient admissions

[Karl Yesler]

Statalist,

I would like to generate a count variable of the number of inpatient admissions per month for each member in a health plan .

I have a panel dataset in the form of medical claims where an individual member may have more than 1 claim per month. I have a variable which indicates whether a claim was inpatient or outpatient (ip_op_cd) and I have the start (from_date2) and end date (to_date2) of that claim. The problem is that a person with one admission to the hospital in that month may have multiple claims during that hospital stay (as indicated by overlapping start and end dates). May I get some help in creating this count variable? I think(?) this would involve identifying each inpatient claim, compare the start date to the end date of any other inpatient claims that month and if it overlaps, ignore it. If it does not overlap generate an indicator variable (which is nice to have). Then do so for each member each month and then count over the indicator variables by each member each month. Claims in which the start date is in one month and the end date is in another month should only be counted as an admission in the earlier month. Months are defined by the from date and are centered on July 2010.

The data might look like the following:

mbr_ckey	cmonth	ip_op_cd	from_date2	to_date2	indicator_admit	ct_admits
1	0	OP	July 1st 2010	July 1st 2010	0	1
1	0	OP	July 3rd 2010	July 3rd 2010	0	1
1	0	IP	July 15th 2010	July 25th 2010	1	1
2	0	IP	July 1st 2010	July 5th 2010	1	2
2	0	IP	July 3rd 2010	July 13th 2010	0	2
2	0	IP	July 29th 2010	Aug 2nd 2010	1	2
2	1	IP	Aug 15th 2010	Aug 15th 2010	1	1

Thanks!

[Roberto Ferrer]

Try something like:

clear
set more off

*----- example data -----

input ///
id     cmonth     str2 ipop     str15(fromd     tod)     indadmit     cntadmit
1     0     OP     "7/1/2010"     "7/1/2010"      0     1
1     0     OP     "7/3/2010"     "7/3/2010"      0     1
1     0     IP     "7/15/2010" "7/25/2010"      1     1
2     0     IP     "7/1/2010"     "7/5/2010"      1     2
2     0     IP     "7/3/2010"     "7/13/2010"      0     2
2     0     IP     "7/29/2010"  "8/2/2010"     1     2
2     1     IP     "8/15/2010"  "8/15/2010"     1     1
end

gen fromd2 = date(fromd,"MDY")
gen tod2 = date(tod, "MDY")
format %td fromd2 tod2
drop fromd tod

list, sepby(id)

*----- what you want -----

bysort id cmonth (fromd2 tod2): ///
    egen totip = total(ipop == "IP" & fromd2 > tod2[_n-1] | ipop == "IP" & _n == 1)

list, sepby(id)

The first observation of each group needs special treatment because there is no previous date to which compare to. In Stata, missings are very large numbers and will always be > the following date.

bysort id cmonth (fromd2 tod2): ///
    egen totip = total(cond(_n==1, ipop == "IP", ipop == "IP" & fromd2 > tod2[_n-1]))

should also work.

[Nick Cox]

The help for egen warns against mixing egen calls with subscripting. But that can be fixed.

  
bysort id cmonth (fromd2 tod2): ///
     gen totip = sum(cond(_n==1, ipop == "IP", ipop == "IP" & fromd2 > tod2[_n-1]))

by id cmonth: replace totip = totip[_N]

[Roberto Ferrer]

Nick,

Thanks for the fix and the reminder. I was aware of the statement in -help-, but it had been some time and it slipped.

I wonder if it would be a good idea for Stata to issue a warning in such cases.

[Nick Cox]

Not my call, but StataCorp's.

I'd say that Stata's philosophy is one of issuing error messages when something's wildly wrong but not of issuing warnings when something is dubious. The presumption is that you know what you are doing. That can be wrong.

Making reference to _n illegal would be a step too far, as that the objection is one of semantics, not syntax.