Calculate In-State-Competition in Compustat

[Theresa Schmitt]

Hi all,

I am trying to calculate a variable "in-state-competition", which is the fraction of total SIC two digit industry sales (excluding the firm itself) generated by in-state competitors.
Can anyone help me with a solution to this? I use panel data that the following Structure:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double fyear str4 sic double sale
"001004" 1993 "5080"        .
"001004" 1994 "5080"  451.395
"001004" 1995 "5080"   504.99
"001004" 1996 "5080"  589.328
"001004" 1997 "5080"  782.123
"001004" 1998 "5080"  918.036
"001004" 1999 "5080" 1024.333
"001004" 2000 "5080"  874.255
"001004" 2001 "5080"  638.721
"001004" 2002 "5080"  606.337
"001004" 2003 "5080"  651.958
end

[Clyde Schechter]

I don't see any way to do this with the data shown. If there is some way to tell which transactions are from competitors, and some way to tell which are in-state, I do not see it. It is also not clear whether by fraction of sales you mean fraction of the number of transactions or fraction of the total value of sales. Please clarify these issues.

[William Lisowski]

Your sample data gives me an idea of what your data is like, but does not contain a variable giving the state; I assume there is such a variable which I call "state" in my code. I assume the firm identifier is gvkey, and the competitors are other firms with the same 2-digit SIC code. I also assume that the 2-digit SIC is to be obtained as the first two characters of the SIC code. The following untested code may start you in a useful direction.

generate sic2 = substr(sic,1,2)
by sic2 state year: egen totsale = total(sale)
generate firmpct = sale/totsale
generate compct = 1 - firmpct

Clyde's reply at post #2, along with my assumptions, makes it clear that you should not assume familiarity with the sort of data you are using; neither Clyde nor I use Compustat data, and only by coincidence am I able to attempt to guess what you mean by firm, competitor, and 2-digit SIC code. And those guesses may be wrong.