Counting multiple identical rows of data only once

Counting multiple identical rows of data only once

by August Anderson -

I have data on the presence of 21 side effects (SE), which was collected from 73 patients over 11,000 total visits during a 2 year time period. 

I want to count the number of unique patients that have each SE. The same patient might have multiple SE during the study period, and might have the same SE over multiple visits. They might have multiple visits on the same day.

A subset of my data is attached. Var "ptid" uniquely identifies the patients, and var "ccenc" uniquely identifies the visits. SE are coded as 0=no 1=yes.

So far, I've tried (using n_sidefxloss, the first SE, as an example):

bysort ptid: egen se1 = count(n_sidefxloss)
bysort ptid ccenc: egen se1 = sum(n_sidefxloss) 
bysort ptid ccenc: egen se1 = total(n_sidefxloss)
bysort ptid ccenc : egen se1 = max(n_sidefxloss)
total n_sidefxloss , over(ptid) 
but they all count every visit that the patient made, so the results are inflated. I want to count each patient only once.
If each patient had the same number of visits, I could divide the count by the number of visits, but the number of visits is not uniform.
Is it possible to tweak this method, so that after finding a SE value==1 in a visit, the count moves on to the next patient?
 
The only solution I've found is to use collapse:
tab n_sidefxloss, generate(selossdummy)
collapse (max) se1count=selossdummy2, by(ptid) 
It works great, but it replaces the data set in memory.

If collapse is a good choice, is there a way to use a macro to sequentially collapse the 21 var and then merge them back into the original dataset?

I tried:
foreach v of var n_sidefx* {
    tab `v', g(`v'dummy)
    collapse (max) `v'count=`v'dummy2, by (ptid)
    save dotsewip_se_d, replace
    merge 1:1 ptid using dotsewip_all
    drop _merge
    save dotsewip_all, replace
    }
but I get an error because ptid doesn't uniquely id the observations (because each patient has multiple visits), so I can't use it to merge. 
If I collapse by(ccenc), which uniquely id's the observations, then the result is inflated because it counts all the observations for the patient.
Thanks!

Re: Counting multiple identical rows of data only once

by Yiwey Shieh -

August, this is more complex than a standard 1 observation per id analysis because you are dealing with a repeated measures issue. Before starting the analysis, you have to tell STATA that this is time-dependent data and each patient has multiple observations assigned to them. As a starting point, I would look into the following commands (just type "help commandname" into STATA):

"stset" - this tells STATA that there is longitudinal data, and lets you set which variable corresponds to longitudinal time - in this case, either visit number or visit date (though you may need to convert the latter into #days of follow-up)

"xtgee" - this is the general command for hierarchical (multiple observations that are linked in some way, in this case due to their relationship to patient id) regression - I assume in this case you are exploring whether side effects are related to drug vs no drug? This is an advanced subject that is introduced in 3 lectures across Biostats 209 in the spring term. I won't begin to try and explain this on the forum - maybe pull one of us aside in class tomorrow, or see if your project mentor has a biostatistician that can help.

Yiwey

Re: Counting multiple identical rows of data only once

by August Anderson -

Hi Yiwey, 

Thanks for pointing me towards tsset. I'll look into it and follow up with you during lab tomorrow.

Is it necessary to use tsset, even though I'm just trying to use descriptive stats, rather than inferential stats?

Another solution I explored was to tag the spells, and then sum them by patient. Is this a viable alternative?

*create var that=1 if the SE is present, and =0 if the SE was not reported
g c_sidefxloss= n_sidefxloss
replace c_sidefxloss = 0 if n_sidefxloss ==. | n_sidefxloss==0
*create a var that=1 when the value of c_sidefxloss changes between 1 and 0
bys ptid: g spell_sidefxloss=(c_sidefxloss!=c_sidefxloss[_n-1])
*sum the number of times that the SE is reported
bys ptid: egen tot_sidefxloss=sum(spell_sidefxloss) if c_sidefxloss==1 & spell_sidefxloss==1

Unfortunately it generates the sum of reported SE, per patient, rather than the sum of SE for all patients, per patient. Is there a way to modify it?

Re: Counting multiple identical rows of data only once

by August Anderson -

Thanks everyone for brainstorming with me!

I found this model, which gave me exactly what I was looking for, without using a loop or tsset

http://www.stata.com/support/faqs/data-management/first-and-last-occurrences/