Duplicate observation of one variable has multiple corresponding observations for another variable; how to keep the lowest value?

[Viktor Christian]

Hello,

I have a dataset which includes bond transaction characteristics of Insurance Companies such as bond rating, issuer ID, bond ID, etc. As you could imagine, 1 bond could have multiple ratings from more agencies hence the dataset could look something like:

Bond_ID Issuer_ID Bond_Rating
1 Moody's 23
1 S&P's 21
1 Fitch 20
2 Moody's 25
2 Fitch 26
2 S&P's 23

I'd like to use a code that would keep the lowest value for the rating of Bond 1 and Bond 2 so that the dataset could look like the following after using the appropriate code:

Bond_ID Issuer_ID Bond_Rating
1 Fitch 20
2 S&P's 23

I have tried looking up a code that corresponds to my needs but I had no luck, perhaps maybe due to my wording in the search bar.

Thank you to anyone who could help,

Viktor Teodorescu

[Daniel PV]

Hi, try this:

clear 
input float Bond_ID str10 Issuer_ID float Bond_Rating
1 "Moody's" 23
1 "S&P's" 21
1 "Fitch" 20
2 "Moody's" 25
2 "Fitch" 26
2 "S&P's" 23
end

bys Bond_ID: egen mini = min(Bond_Rating)
keep if Bond_Rating==mini

[Nick Cox]

Thanks to Daniel PV for working up a dataex example.

Here is another way to do it. Note that Daniel's code will keep results tied for minimum.

 
 clear  input float Bond_ID str10 Issuer_ID float Bond_Rating 1 "Moody's" 23 1 "S&P's" 21 1 "Fitch" 20 2 "Moody's" 25 2 "Fitch" 26 2 "S&P's" 23 end  bysort Bond_ID (Bond_Rating) : keep if _n == 1