bysort egen - sum with conditions

[Rochelle Zhang]

Dear statalisters,

Below is an illustration of my data structure. I want to total segsales for each year, segsic, combination , but only for observations that have priseg =1 (a flag),

e.g. for year 1999, I expect totalsale = 20 + 25+50 (since firm 1001, 1002, 1003 share the same 4100 in year 1999).

For year 2000, I expect totalsale for 4200 segsic=21+22, and totalsale for 4300 segsic=33


input firmid year segsales segsic priseg
1001 1999 20 4100 1
1001 1999 10 4200 0
1001 1999 . 5100 .
1002 1999 25 4100 1
1002 1999 15 5100 0
1003 1999 50 4100 1
1003 1999 31 4200 0
1001 2000 19 4100 0
1001 2000 21 4200 1
1001 2000 . 5100 .
1002 2000 22 4200 1
1002 2000 13 5100 0
1003 2000 33 4300 1
end

I am not sure if I need generate another variable using something like egen group (segsic priseg) to flag my observations.

Thank you,
Rochelle

[Clyde Schechter]

egen totalsale = total(segsales*(priseg==1)), by(year segsic)

should do it.

[Nick Cox]

Clyde's trick is the neatest here, exploiting the fact that multiplying by 1 or 0 (the result of evaluating the true or false expression priseg == 1)
leaves values you want unchanged and and maps values you don't want to 0. For a total, that is exactly what you want.

Two more general tricks are

  
egen totalsale = total(cond(priseg == 1, segsales, .)), by(year segsic)

and

egen totalsale = total(segsales / (priseg==1)), by(year segsic)

I say more general because even for calculating means the zeros are a nuisance and you need missings to get the right answer.

More discussion within http://www.stata-journal.com/article...article=dm0055

[Rochelle Zhang]

Many Thanks to both of you !!!


Rochelle