Total with missing values

[Raph Selenite]

Hello,

I would like to do the total bys of a variable that contains missing value. But i don't want to use missing as zeros.

My database is like that :

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id recipe x wanted)
1 1 . 5
1 2 1 5
1 2 2 5
1 2 2 5
2 1 . 6
2 2 2 6
2 2 4 6
3 1 . .
3 2 2 .
3 2 . .
3 2 3 .
4 1 . 4
4 2 4 4
5 1 . .
5 2 . .
5 2 3 .
5 2 . .
end
label values recipe recipe
label def recipe 1 "name", modify
label def recipe 2 "ingredient", modify

So i want to get the same variable as "wanted".
I want to do the sum of x by id, but only if every x are not missing (every x exect if recipe==1, because it's always a missing value)

Is it possible ?

Thanks

[Joro Kolev]

I do not understand neither what you are explaining, nor your data example.

Can you please try again to explain how you want the total to be computed?

[Nick Cox]

I am puzzled at #1 which seems contradictory. But to back up:

Stata is inclined to ignore missings in calculating sums or totals. That is exactly equivalent to treating them as zero.

If you don't want that, you need to count missings and do something different. Consider

egen missing = total(missing(x)), by(id)
egen total = total(x), by(id)
replace total = . if missing

[Carlo Lazzaro]

Ralph:
probably late to the party with a bit inefficient code:

. bysort id : gen counter=0

. bysort id: replace counter=1 if x==. & recipe==2

. sort id counter

. bysort id: replace counter=counter[_N] if counter[_N]==1


. bysort id: egen wanted2=total(x) if counter==0

. drop counter

. list

     +----------------------------------------+
     | id       recipe   x   wanted   wanted2 |
     |----------------------------------------|
  1. |  1   ingredient   2        5         5 |
  2. |  1   ingredient   2        5         5 |
  3. |  1         name   .        5         5 |
  4. |  1   ingredient   1        5         5 |
  5. |  2         name   .        6         6 |
     |----------------------------------------|
  6. |  2   ingredient   4        6         6 |
  7. |  2   ingredient   2        6         6 |
  8. |  3   ingredient   3        .         . |
  9. |  3         name   .        .         . |
 10. |  3   ingredient   2        .         . |
     |----------------------------------------|
 11. |  3   ingredient   .        .         . |
 12. |  4   ingredient   4        4         4 |
 13. |  4         name   .        4         4 |
 14. |  5         name   .        .         . |
 15. |  5   ingredient   3        .         . |
     |----------------------------------------|
 16. |  5   ingredient   .        .         . |
 17. |  5   ingredient   .        .         . |
     +----------------------------------------+

.

[Raph Selenite]

Sorry if my request was not very clear

Thanks Carlo, that was exactly what i needed !

Have a nice day

[Nick Cox]

egen total = total(x) if !missing(x), by(id)

is not quite what is wanted here, but worth noting.

[Hemanshu Kumar]

An alternative to #4:

sort id recipe
gen _sum = 0
by id: replace _sum = cond(_n == 1, x, x + _sum[_n-1]) if recipe != 1
by id: egen wanted3 = max(_sum), missing
drop _sum

Incidentally, I get all missing values for total if I run the code in #3.

[Nick Cox]

#7 is totally correct about #3. That is because the code is designed to give missing values for totals if there are any missing values at all. That was a guess at the real problem, which was quite wrong in this case, but it is occasionally exactly what people ask for.