Summing aggregate fractions in groups per year to use in calculation

[Daniel Hendriks]

YEAR	CUSIP	PATENT	CAT	CITING	CITINGCAT
1990	10	3b	ZL	9x	ZP
1990	10	3b	ZL	10t	ZL
1991	10	4c	ZP	8y	ZI
1991	10	5d	ZU	7u	VT
1992	10	6e	ZI	13i	VT

Hello everyone,

The above table is a created representation of my data. Cusip is the firm identifier; patent is a patent number; category is the category in which the patent belongs to; citing is the patent number that the variable patent cites to; and citingcat is the category of the patent (citing) that patent makes a citation to.

I think I would like to have it look like the below table. The ultimate variable I want is a measure of proximity of its patents per year. Variable meanings in this table are: npattott-1 are the total number of patents before the year; npattott are the total patents after the year; catactt-1 are the categories in which the firm has patents before the year; catactt-1 are the categories in which the firm has patents after the year.

Proximity is calculated the following way: fractiont*fractiont-1/(((fractiont²)*( fraction t-1²))^^1/2)
Example for category ZL in 1992: (3/5*2/4 / ((3/5²)*(2/4²))^1/2) = 1,66
Then taking the sum of all these outcomes for the categories per year would give the measure of proximity each year.

YEAR	CUSIP	PATENT	CAT	NPATTOTt-1	NPATTOTt	CATACTt-1	CATACTt	CITING	CITINGCAT	Proximitypatent
1990	10	3b	ZL	0	2	.	ZL(2x)	9x	ZP
1990	10	3b	ZL	0	2	.	ZL(2x)	10t	ZL
1991	10	4c	ZP	2	4	ZL(2x)	ZL(2x), ZP,	8y	ZI
1991	10	5d	ZU	2	4	ZL(2x),ZP,	ZL(2x), ZP, ZU	7u	VT
1992	10	6e	ZL	4	6	ZL(2x), ZP, ZU,	ZL(3x), ZP, ZU,	13i	VT
1992	10	6e	ZR	4	6	ZL(3x), ZP, ZU,	ZL (3x), ZP, ZU, ZR	16u	ZL

I struggle to do this in an efficient way or in a way at all. The thing making it harder is also that there are around 400 categories of patents/cites. This can however be subdivided in around 60 categories.

Is there a way to do this? (I hope I have explained clearly enough). After I would be able to do this I would collapse a measure of technological proximity per year per firm.

Thanks a lot in advance.

[Phil Bromiley]

You didn't get a quick answer. You'll increase your chances of a useful answer by following the FAQ on asking questions - provide Stata code in code delimiters, readable Stata output (fixed spacing fonts help), and sample data using dataex. Also simplify your problem to the minimum necessarily to illustrate your problem. Delete any variables or terms you don't need. Do you have varying numbers of patents per firm-year? Your question is exceedingly complicated.

You may want to try to start a new post providing a very simplified example that illustrates your problem along with (potentially hypothetical) data to work with.