identifying unique patients among many observations

[Birgitte Tholin]

Hi ,

Have a huge dataset of 4 mill observation on patients with covid and a control group to investigate the incidence of VTE (blod clots).
One horizontal line represents one episode, most patient have several episodes but all have a unique ID.

For example:

ID Episode age test date sex group activity VTE

111 EP13042 27 16.03.21 male covid inpatient no
112 EP10948 19 18.07.21 female control inpatient no
111 EP93292 28 16.03.21 male covid outpatient yes


I want to do descriptive statistics on i.e how many covid vs controls were outpatients vs inpatient etc. Further on, what was the mean age in that group, mean gender etc. This should be at the time they tested positive for Covid, not at later contacts with hospital. We have episodes after a positive test to later on describe the 90-day incidence of VTE.

If I use tab group if group==”covid” & activity =”outpatient” I end up with the nr of episodes of outpatients with covid, but how do I find the unique nr of patients? Do I need to make a new variable marking unique patientIDs?

Further on I would use sum age if group==x & activity==x to find the mean age, but then again I get the mean age of all the episodes, not of unique patients. The age of the patients also changes, so I need to make the new variable with marking on the right episode (first positive test date).

Any suggestions on how to solve this? Both summing up nr of unique patients based on group and activity, and finding mean age, gender etc in that specific population.

Sorry if anything is unclear. Quite new to this. Thanks in advance.

Best regards,
Birgitte

[Andrew Musau]

You need the -tag()- function of egen.

egen tag= tag(ID group activity)
tab group if group=="covid" & activity =="outpatient" & tag
sum age if group=="covid" & activity=="outpatient" & tag