collapse command yielding incorrect means

[Akshar Rambachin]

Hi all
First time posting (sorry in advance for any mistakes in how to post, will edit of course!)

I am having an issue using the collapse command for a large dataset of ~700,000 observations across 15,000 people. Each observation is for a person with a given pain score. I want to find the average per person. Each person has a different number of pain scores in the dataset (some have <5 and some have >100).

The code I used is below

collapse pain_score by (person)

This seems to work and correctly collapses the dataset to the correct number of people with a given average for pain score for each person. However, when I spot check a few people, the averages are all slightly off. For example, Person 1 has 6 observations which are 6, 6, 0, 0, 0, 0 which should be an average of 2, but the mean in Stata is 2.333. There are similar discrepancies throughout.

Thank you in advance!

[Rich Goldstein]

note that "by()" is an option and thus goes after a comma; also, there should not be a space between "by" and "("; however, it is not possible to say if either of these, or both together, are the problem without a data example using -dataex- and posted with CODE blocks; please read the FAQ

[Akshar Rambachin]

note that "by()" is an option and thus goes after a comma; also, there should not be a space between "by" and "("; however, it is not possible to say if either of these, or both together, are the problem without a data example using -dataex- and posted with CODE blocks; please read the FAQ

Let me try again- read the FAQ, thank you!

dataex

input long person float pain_score
1 0
1 0
1 0
1 4
1 5
1 0
1 2
2 1
2 0
2 3
3 0
3 0
3 8
3 0

1 is person 1, with 7 separate pain values accordingly (0, 0, 0, 4, 5, 0, 2)
2 is person 2 and so on

The code I ran was

Code:
collapse pain_score, by (person)

[Carlo Lazzaro]

Akshar:
have you compared -collapse- results with the following ones?

. mean pain_score, over(person)

Mean estimation                                    Number of obs = 14

---------------------------------------------------------------------
                    |       Mean   Std. err.     [95% conf. interval]
--------------------+------------------------------------------------
c.pain_score@person |
                 1  |   1.571429   .8123201     -.1834823    3.326339
                 2  |   1.333333   .8819171     -.5719327    3.238599
                 3  |          2          2     -2.320737    6.320737
---------------------------------------------------------------------

.

[Akshar Rambachin]

Hi Carlo-

Great idea. I tried this morning and the collapse results are identical to the results from the "mean pain_score, over(person)"

I again spot checked manually and the numbers seem to be consistent. I am really not sure what changed, but I appreciate the advice and this approach helped me double check effectively. Thank you!