CODEWITHSUNDEEP
stata
Dirk Douwes
Dirk Douwes

Reputation: 33

Collapsing sum of variable conditional on another variable

I am working with the CES diary data from 2006. I have a file which for each household has an entry for each item bought during a week long period. I have the following variables

newid id of household

cost dollar cost of item

ucc a code denoting the type of item

I am interested in restaurant expenditures which is covered by ucc 190111, 190112, ... . I want to collapse my data so for each newid I have the sum of restaurant expenditures for the household during the week. I used the command 

 collapse (sum) cost if ucc=="190111".... , by (newid)

However, I would like to have a zero when there are no restaurant expenditures and Stata simply removes those entries.

Upvotes: 0

Views: 1976

Answers (1)

dimitriy
dimitriy

Reputation: 9460

You need an intermediate variable with some zeros for non-restaurant expenditures:

gen rest_exp = cond(inlist(ucc,"190111","190112"),cost,0)
collapse (sum) rest_exp, by(newid)

One caveat is that inlist() has a constraint of 9 possible values for strings, but you probably have fewer than that or should destring, in which case the limit is 254. You can also hitch a few inlist()s together with |.

Upvotes: 1

Related Questions