Generate a count Variable from three variables

[Jana Schue]

Hello

I have the variables "year" (2009-2019), "company key" and "investment type" (e.g. green, biotechnology, ...) and want to create a count variable that shows me how many green investments have been made per company per year. How can I do that?

E.g. Company X had 25 green investments in the year 2009.

Thank you for your help!

Best,
Jana

[Andrew Musau]

Welcome to Statalist. The code depends on whether "investment type" is a string variable or a numeric variable with value labels. If the former

bys company_key year: egen wanted= total(investmet_type=="green")

If numeric

lab list

to see what value is labeled "green". Assuming green=2

bys company_key year: egen wanted= total(investmet_type==2)

[Noah Mkasanga]

Jana,
Just an addition to Andrew's code. If investment_type has many values, you may also consider the following:
clear
input comp_key str20 inv_type year
3 "green" 2009
2 "biotechnology" 2010
end

encode inv_type, gen( inv_type_)
levelsof inv_type_, local(levels)
foreach x in `levels' {
bys comp_key year: egen wanted_`x'= total(inv_type_==`x')
}

[Jana Schue]

Thank you so much