How can I find the difference between rows based on a certain condition? (Panel data)

[David Lade]

Here is my example data set:

clear
input byte id float date byte(month portfolio) float(return size_ratio) 
1 175  1 10 0.01 3.2
2 175  1 10  0.04 1.2
3 175  1 1  0.12 1.8
1 176  2 2  0.04 4.3
2 176  2 1  0.16 1.4
3 176  2 10 0.03 7.4
1 177  3 7  0.1  3.7
2 177  3 10 -0.2 9.5
3 177  3 1  0.15 2.6
end

I want to create 2 new columns, based on 'return' and 'size_ratio' such that it it will find the difference between the values if the corresponding portfolio values are 10 or 1. If there are multiple firms in either portfolio, I want to take the sample average for their corresponding value

So for instance, for

date = 175

,

return = (0.01+0.04)/2 - 0.12

size_ratio = (3.2+1.2)/2 - 1.8

For

 data=176

return = 0.03 - 0.16

size_ratio = 7.4 - 1.4

Then the output would be something like:

clear
input byte id float date byte(month portfolio) float(return size_ratio return_difference size_difference) 
1 175  1 10 0.01 3.2 -0.095 0.4
2 175  1 4  0.04 1.2 -0.095 0.4
3 175  1 1  0.12 1.8 -0.095 0.4
1 176  2 2  0.04 4.3 -0.13  5
2 176  2 1  0.16 1.4 -0.13  5
3 176  2 10 0.03 7.4 -0.13  5
1 177  3 7  0.1  3.7 -0.35  6.9
2 177  3 10 -0.2 9.5 -0.35  6.9
3 177  3 1  0.15 2.6 -0.35  6.9
end

Any help would be very much appreciated.

[Clyde Schechter]

clear
input byte id float date byte(month portfolio) float(return size_ratio)
1 175  1 10 0.01 3.2
2 175  1 10  0.04 1.2
3 175  1 1  0.12 1.8
1 176  2 2  0.04 4.3
2 176  2 1  0.16 1.4
3 176  2 10 0.03 7.4
1 177  3 7  0.1  3.7
2 177  3 10 -0.2 9.5
3 177  3 1  0.15 2.6
end

foreach v of varlist return size_ratio {
    by date, sort: egen `v'10 = mean(cond(portfolio == 10, `v', .))
    by date, sort: egen `v'1 = mean(cond(portfolio == 1, `v', .))
    gen `v'_diff = `v'10 - `v'1
}

Note: this produces the same results you show in your example, except for date 176 and size_difference. Your calculation of 4 is an error: 7.4 - 1.4 = 6, not 5.

[David Lade]

clear
input byte id float date byte(month portfolio) float(return size_ratio)
1 175 1 10 0.01 3.2
2 175 1 10 0.04 1.2
3 175 1 1 0.12 1.8
1 176 2 2 0.04 4.3
2 176 2 1 0.16 1.4
3 176 2 10 0.03 7.4
1 177 3 7 0.1 3.7
2 177 3 10 -0.2 9.5
3 177 3 1 0.15 2.6
end

foreach v of varlist return size_ratio {
by date, sort: egen `v'10 = mean(cond(portfolio == 10, `v', .))
by date, sort: egen `v'1 = mean(cond(portfolio == 1, `v', .))
gen `v'_diff = `v'10 - `v'1
}

Note: this produces the same results you show in your example, except for date 176 and size_difference. Your calculation of 4 is an error: 7.4 - 1.4 = 6, not 5.

Thank you so much for your help - and I apologise for the confusion.