Difference in date between grouped observations

[Diana Sellevold]

Hi!

I am working on a dataset of bonds of 2 types - ESGBond variable "Yes/No". Observations are matched and placed in groups (GroupID).
Each group has one/several non-ESG bond (like observation 1 and 2) and the last observation in a group is an ESGBond "Yes" (eg. observation nr. 3)

In each group, I want to calculate the difference between the Maturity date of non-ESG bonds, and the Maturity date of the last observation in each group. If it exceeds 4 years, observation must be dropped (like observation nr. 1)

Similar for the AmountIssuedUSD: if a non-ESG observation in a group is > 4 times the ESG bond, the observation must be dropped. Also, if a non-ESG obs in a group is < 1/4 times the ESG bond, the observation must be dropped.

Thanks in advance!

* Example generated by -dataex-. For more info, type help dataex
clear
input double Maturity long AmountIssuedUSD int GroupID str3 ESGBond
22154.000243055554  11665508  1 "No" 
22695.000243055554  15000000  1 "No" 
23535.000243055554  20000000  1 "Yes"
23425.000243055554  45000000  2 "No" 
22695.000243055554  15000000  2 "No" 
23076.000243055554  50006468  2 "Yes"
22269.000243055554  17761051  3 "No" 
23393.000243055554  50000000  3 "Yes"
22269.000243055554  11147177  4 "No" 
22644.000243055554  40000000  4 "No" 
23708.000243055554  37810860  4 "Yes"
23330.000243055554 128639423  5 "No" 
24058.000243055554 128639423  5 "No" 
24092.000243055554  23338362  5 "No" 
22929.000243055554 160124683  5 "No" 
24247.000243055554  80399639  5 "Yes"
24457.000243055554  85130533  6 "No" 
24469.000243055554 189178963  6 "No" 
22974.000243055554  47709923  6 "No" 
22441.000243055554  32442748  6 "No" 
22806.000243055554  32442748  6 "No" 
24090.000243055554 189178963  6 "Yes"
24821.000243055554  57525263  7 "No" 
23930.000243055554  49279533  7 "Yes"
24583.000243055554  68991346  8 "No" 
24295.000243055554  70873731  8 "Yes"
23726.000243055554  38350175  9 "No" 
25314.000243055554  67177213  9 "No" 
25026.000243055554  73919299  9 "Yes"
23407.000243055554  45158539 10 "No" 
23482.000243055554 135669431 10 "No" 
24138.000243055554  90510892 10 "No" 
25373.000243055554 193813473 10 "No" 
26164.000243055554 484533684 10 "No" 
26433.000243055554 121133421 10 "No" 
26433.000243055554 164741452 10 "No" 
24972.000243055554 174432126 10 "No" 
24418.000243055554 145360105 10 "Yes"
26164.000243055554 290720210 11 "No" 
25149.000243055554 145360105 11 "Yes"
23934.000243055554       195 12 "No" 
23934.000243055554 287902343 12 "Yes"
23934.000243055554       195 13 "No" 
24664.000243055554 377993233 13 "Yes"
23699.000243055554 375093773 14 "No" 
23894.000243055554 412603150 14 "No" 
24630.000243055554 900225056 14 "No" 
24784.000243055554 450112528 14 "No" 
25478.000243055554 750187546 14 "No" 
26611.000243055554 675168792 14 "No" 
31006.000243055554 187546886 14 "No" 
33501.000243055554 300075018 14 "No" 
33916.000243055554 375093773 14 "No" 
26927.000243055554 825206301 14 "Yes"
24601.000243055554  75018754 15 "No" 
24601.000243055554 223031744 15 "Yes"
24601.000243055554  75018754 16 "No" 
24601.000243055554 150037509 16 "Yes"
24295.000243055554 600150037 17 "No" 
24863.000243055554 450112528 17 "No" 
24897.000243055554 450112528 17 "No" 
25324.000243055554 750187546 17 "No" 
25617.000243055554 450112528 17 "No" 
25847.000243055554 375093773 17 "No" 
32207.000243055554 356339084 17 "No" 
32919.000243055554 600150037 17 "No" 
33332.000243055554 375093773 17 "No" 
26249.000243055554 562640660 17 "Yes"
25458.000243055554 262565641 18 "No" 
33859.000243055554 412603150 18 "No" 
26617.000243055554 825206301 18 "Yes"
22856.000243055554 589344648 19 "No" 
22876.000243055554 589344648 19 "No" 
23503.000243055554 293190647 19 "Yes"
23125.000243055554 293190647 20 "No" 
23308.000243055554 219892985 20 "No" 
23630.000243055554 131935791 20 "No" 
23801.000243055554 293190647 20 "No" 
22201.000243055554 295277043 20 "No" 
22424.000243055554 295277043 20 "No" 
22613.000243055554 221457782 20 "No" 
23504.000243055554 219892985 20 "Yes"
23164.000243055554  73297661 21 "No" 
23629.000243055554  73297661 21 "No" 
22130.000243055554  70288887 21 "No" 
22303.000243055554  42173332 21 "No" 
22537.000243055554  70288887 21 "No" 
22408.000243055554  28115554 21 "No" 
22078.000243055554  70288887 21 "No" 
23506.000243055554  43978597 21 "Yes"
23085.000243055554 366488309 22 "No" 
23475.000243055554 366488309 22 "No" 
23623.000243055554 293190647 22 "No" 
23747.000243055554 366488309 22 "No" 
23749.000243055554  73297661 22 "No" 
23810.000243055554 293190647 22 "No" 
23846.000243055554 366488309 22 "No" 
24540.000243055554 146595323 22 "No" 
22107.000243055554 368134295 22 "No" 
22487.000243055554 368134295 22 "No" 
end
format %tdnn/dd/CCYY Maturity

[Ken Chui]

This hopefully would get you started but do check the position on which subtract which and which divided by which:

* Extracting ESG date:
egen esg_date = max(Maturity*(ESGBond=="Yes")), by(GroupID)
format %tdnn/dd/CCYY esg_date
* Generating date difference:
gen date_diff = datediff(Maturity, esg_date, "year")

* Extracting dollar:
egen long esg_amount = max(AmountIssuedUSD*(ESGBond=="Yes")), by(GroupID)
* Compute fraction:
gen amount_ratio = AmountIssuedUSD / esg_amount
* Drop cases:
drop if ESGBond == "No" & (amount_ratio >= 4 | amount_ratio < .25)

Notice that I didn't suggest any code regarding dropping basing time duration. There are a lot of weird numbers and I'm not sure which direction is right. With the difference in year generated it should be apparent to drop case using drop command.

[Diana Sellevold]

Hi Ken!

You are a lifesaver! Your code works perfectly and is exactly what I was looking for Thank you so so much!!!