Creating a count variable with the option of excluding focal firm-year observation

[Christelle Alkhoury]

Hello,

I want to create a variable that counts the number of attacks in the same state, but excludes the attack of the specific firm that is in the observation at time t (i.e, n_state_other_firms).

I have the variables: state, year, gvkey (company identifier) and n_state (number of attacks in the same state in the same year)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 state int year long gvkey float n_state
"AK" 2014 126555  1
"AL" 2014  61055  1
"AL" 2015   4674  1
"AL" 2016  11228  1
"AR" 2016  11259  3
"AR" 2016  11259  3
"AR" 2016  11259  3
"AZ" 2016  17934  1
"CA" 2014   1602 10
"CA" 2014  12796 10
"CA" 2014  30293 10
"CA" 2014  31843 10
"CA" 2014 111662 10
"CA" 2014 114524 10
"CA" 2014 117768 10
"CA" 2014 152149 10
"CA" 2014 175376 10
"CA" 2014 185419 10
"CA" 2015  19661  6
"CA" 2015  27928  6
"CA" 2015  27928  6
"CA" 2015  30281  6
"CA" 2015 152149  6
"CA" 2015 178083  6
"CA" 2016   8007  8
"CA" 2016  13988  8
"CA" 2016  19661  8
"CA" 2016  20779  8
"CA" 2016  22704  8
"CA" 2016  63083  8
"CA" 2016 150937  8
"CA" 2016 176782  8
"CA" 2017   2721 10
"CA" 2017   8007 10
"CA" 2017  12796 10
"CA" 2017  17376 10
"CA" 2017  18562 10
"CA" 2017  24905 10
"CA" 2017  30655 10
"CA" 2017  63178 10
"CA" 2017 162733 10
"CA" 2017 185419 10
"CO" 2015  11922  1
"CO" 2016  18047  2
"CO" 2016 175263  2
"CT" 2014  10905  1
"CT" 2015   1177  5
"CT" 2015   2547  5
"CT" 2015   2547  5
"CT" 2015   5723  5
"CT" 2015 119314  5
"CT" 2016   1177  1
"CT" 2017   1177  6
"CT" 2017   1177  6
"CT" 2017   1177  6
"CT" 2017   1177  6
"CT" 2017 125240  6
"CT" 2017 183388  6
"DE" 2017  12673  1
"FL" 2014   9063  3
"FL" 2014  30536  3
"FL" 2014 260778  3
"FL" 2015 163916  2
"FL" 2015 174729  2
"FL" 2016  21440  2
"FL" 2016 190355  2
"FL" 2017   8210  4
"FL" 2017 165993  4
"FL" 2017 260778  4
"FL" 2017 260778  4
"GA" 2014   3144  8
"GA" 2014   5680  8
"GA" 2014   5680  8
"GA" 2014   5680  8
"GA" 2014   5680  8
"GA" 2014  10187  8
"GA" 2014  30888  8
"GA" 2014  30888  8
"GA" 2015   1414  4
"GA" 2015   1449  4
"GA" 2015   4423  4
"GA" 2015  20067  4
"GA" 2016   1414  3
"GA" 2016   1449  3
"GA" 2016  30029  3
"GA" 2017   4423  3
"GA" 2017   4423  3
"GA" 2017  10187  3
"IA" 2015 145701  1
"IA" 2017 145701  2
"IA" 2017 145701  2
"ID" 2017  16486  1
"IL" 2014   7982  4
"IL" 2014  11264  4
"IL" 2014  11264  4
"IL" 2014 158737  4
"IL" 2015   3221  7
"IL" 2015  10795  7
"IL" 2015  11264  7
"IL" 2015  11264  7
end

For example, manually computing the variable for the below portion of the dataex output:

state year gvkey n_state
"CA" 2015 19661 6
"CA" 2015 27928 6
"CA" 2015 27928 6
"CA" 2015 30281 6
"CA" 2015 152149 6
"CA" 2015 178083 6

n_state_other_firms would be=5 for each of gvkey 19661, 30281, 152149 and 178083 in 2015.
But, n_state_other_firms would be =4 for gvkey 27928 in 2015.

How can I get this variable on stata?

P.S. I am using Stata/SE16.0

Thank you in advance for your time and consideration!

[Ken Chui]

gen count = 1
bysort year state: egen v1 = total(count)
bysort year state gvkey: egen v2 = total(count)
gen wanted = v1 - v2

[Nick Cox]

If you're counting observations, you can be even more direct.

bysort year state gvkey : gen v2 = _N
by year state: gen v1 = _N 
gen wanted = v1 - v2

[Ken Chui]

If you're counting observations, you can be even more direct.

bysort year state gvkey : gen v2 = _N
by year state: gen v1 = _N
gen wanted = v1 - v2

_N is a good idea, I tend to forget that.

[Christelle Alkhoury]

Thank you Ken and Nick!
I used the _N and now I have the variable.