Loop for creating number of prior events within a specific time frame

[Marvin Hanisch]

Dear all,

I am trying calculate the number of alliances that a given firm formed with other partners five years before forming the focal alliance. My dataset has the variables firm_1, firm_2, and alliance_year. Importantly, the counter should ignore alliances that were formed in the same year with other partners as well as past alliances that were formed with the same partner as in the focal alliance. Here is an example of the dataset and the variable count_firm_1 (same for firm_2) that I would like to create:

Example:

id	firm_1	firm_2	alliance_year	count_firm_1	explanation
1	a	b	2007	0	none before 2007
2	a	c	2007	0	none before 2007
3	a	d	2008	2	prior alliances with b and c
4	a	d	2011	2	prior alliances with b and c (alliance with the same partner d is ignored)
5	a	e	2014	1	only alliance with d in 2011 is counted - all other alliances date back more than five years
6	x	b	2011	0	none before 2011
7	x	c	2012	1	prior alliance b
8	x	d	2013	2	prior alliances with b and c
9	x	e	2013	2	prior alliances with b and c (alliance in the same year with d is ignored)
10	x	e	2013	2	prior alliances with b and c (alliances in the same year / with the same are ignored)
11	x	f	2014	3	prior alliances with b, c and e (alliance with e in the same year is counted only once; alliance with e would be counted twice if the alliances between x and e were in different years)
12	x	d	2015	4	prior alliances with b, c, e, and f

I have tried various solutions that were posted on related topics but none of those seemed to work for me. Any help would be appreciated.

Best,

Marvin

[Clyde Schechter]

I think I have what you want here:

encode firm_1, gen(nfirm_1)
encode firm_2, gen(nfirm_2)

capture program drop one_time
program define one_time
    drop if nfirm_2 == pfx_nfirm_2
    by nfirm_2, sort: keep if _n == 1
    gen wanted = _N
    exit
end

rangerun one_time, by(nfirm_1) interval(alliance_year -5 -1) ///
    use(nfirm_2 alliance_year) sprefix(pfx_)
    
replace wanted = 0 if missing(wanted)

Notes:

1. To use this code you must install the -rangerun- program, by Robert Picard, available from SSC. And in order to use -rangerun- you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also available from SSC. These programs are very useful generally and worth having regardless of this particular application.

2. This code agrees with your count_firm_1 variable in all cases except observation 11. I believe your entry for observation 11 is not consistent with your explanation of what you want. Looking at x's previous alliances there, I see alliances with firms b, c, d, and e, a total of 4. In your explanation you make no mention of d and you give your count as 3. But the x-d alliance is distinct from the others, is distinct from the b-f alliance in observation 11 itself, and is within the 5 year window. So I don't think it should have been excluded. If I have misunderstood, please clarify and I will try to fix the code accordingly.

3. Notice that this code has no explicit loop. That is because -rangerun- itself contains a mechanism that loops over individual observations, but does so in a particularly efficien way.

4. -rangerun- does its work with numeric variables, not strings. That is why I have -encode-d the two firm variables. I don't know how large your data set is -encode- has a limit on the number of distinct values it can handle in a variable (having to do with the number of values in a value label). If you have too many firms in your data set for this, then you can instead do

egen long nfirm_1 = group(firm_1)
egen long nfirm_2 = group(firm_2)

This will produce the same numeric results, but the results will not have value labels.

[Marvin Hanisch]

Dear Clyde,

Thank you so much for your help, the suggested code and your explanations. The solution worked perfectly and was computationally very efficient!

Regarding your second point, the "3" was my mistake and should have been "4" - thanks for pointing this out.

Best,

Marvin

[Romalpa Akzo]

The solution of Clyde sensei is almost perfect and very "beautiful" once you get familiar with -rangerun- . Indeed, I do try to follow it, do learn something from it and now do like it a lot.

I also contribute a solution with a loop and -duplicates-, which is also effective and meaningfully applicable for similar issues.

gen wanted =.

forval i=1/`=_N' {

duplicates report firm_2 if _n==`i' | (firm_1==firm_1[`i'] ///
& inrange(alliance_year, alliance_year[`i'] -5, alliance_year[`i']-1))

replace wanted = `r(unique_value)' - 1  in `i'
}

[Marvin Hanisch]

Dear Romalpa,

Thanks for the alternative! This also works very nicely.

I have a quick follow-on question. Can I somehow account for the fact that firms may appear in column firm_1 and firm_2? For example, in the last two rows of the table below, firms "b" and "c" appear in column firm_2 in row 1 and 2. In other words, the firm names should be temporarily sorted across columns.

id	firm_1	firm_2	alliance_year	count_firm_1	explanation
1	a	b	2007	0	none before 2007
2	a	c	2007	0	none before 2007
...
13	b	a	2008	0	b had an alliance with a in 2007 (not counted)
14	c	d	2009	1	c had an alliance with a in 2007 (counted)

I am sorry to make this more complicated. I would appreciate your help once more.

Best,

Marvin

[Romalpa Akzo]

help rowsort

[Marvin Hanisch]

Easier than I thought. Thanks.