Problem with sum of observations by ID

[Firangiz Aghayeva]

Dear Forum users,

I am trying to sum up the investment size by investor ID using "collapse(sum) investment, by(ID)". But this function sums up the missing values as well, so I am getting zeros where it should be missing value in the total investment variable. I tried if function, but STATA doesn't allow it with collapse. Is there any idea on how I can solve this issue?

Kind regards,
Firangiz Aghayeva

[Nick Cox]

If you want to insist that missing values result in missing totals, you need an extra step

clonevar investment2 = investment 
bysort ID (investment2) : replace investment2 = . if missing(investment2[_N])

and then collapse using investment2

if is a qualifier or a command depending on what you tried; it's not a function.

See also https://www.statalist.org/forums/help#spelling

[Firangiz Aghayeva]

Dear Nick,

Thank you for your quick reply. I tried the command you suggested, however it does not work. I still get the zeros.

Thank you for correcting me. I will have a look at the link you sent.

Kind regards,
Firangiz

[Nick Cox]

Indeed. We need to work harder at it then. Something like this:

clonevar investment2 = investment  
bysort ID (investment2) : replace investment2 = . if missing(investment2[_N])
by ID : gen allmissing = missing(investment2[1])  

collapse (sum) investment2 (mean) allmissing, by(ID)  

replace investment2 = . if allmissing

[Firangiz Aghayeva]

Dear Nick,

Thank you for the code. It works well, no zeros where there should be missing values. But now it sums up incorrectly for some IDs, showing zero, where there should be a real number (sum). I can't correct the code because, I did not quite understand the part where you generated allmissing and in the end, used (mean) allmissing. Would you mind elaborating more on the code so that I can also make corrections for it to work for 100%?

Kind regards,
Firangiz