How to generate a count occurencies of a specific value by id?

[Katy Quevedo]

I have a data set containing a series of 5 questions with 3 response options each (Yes, No, I don't know) as follows:

ID	Q1	Q2	Q3	Q4	Q5
101	Yes	IDK	Yes	Yes	Yes
102	Yes	No	Yes	No	Yes
103	Yes	No	Yes	IDK	Yes
104	No	No	No	Yes	Yes
105	Yes	Yes	Yes	Yes	Yes
106	Yes	IDK	IDK	IDK	IDK

I want to generate a new variable with the number of times each participant chose IDK. Something like this:

ID	#IDK
101	1
102	0
103	1
104	0
105	0
106	4

Is there any way I can do this using egen?

[Andrew Musau]

With few categories, you can do something like

gen wanted= (Q1=="IDK")+ (Q2=="IDK")+ (Q3=="IDK")+ (Q4=="IDK")+ (Q5=="IDK")

But in general, you want to loop

gen wanted=0
foreach var of varlist Q1-Q5{
    replace wanted= wanted + (`var'=="IDK")
}

Another possibility is to reshape long the data, keep observations with Q="IDK" and contract the dataset. You could do this in a new frame and keep your data intact. For the most part, egen functions are not optimized for string inputs.

[Nick Cox]

Andrew Musau is right if your variables are string, as they appear to be. Otherwise you can reach for the anycount() function of the egen command.