Finding Corresponding Values on a Datasheet

[Hans Schubert]

Hello,

Would appreciate some help with this do-file please.

1 - Start by looking at the value in the maxTFRALL column
2 - Code required then looks back through spdrat column until it finds the value that is identical to the one in the maxTFRALL column
3- Code required then writes into the wanted column the corresponding value in the racetyperuns_invert column.

example for row 12
maxTRFALL value is 120
go to spdrat column and look back until you find 120
write into the wanted column the corrosponding racetyperuns_invert value - which is "2" is this case

I hope thats clear

Thanks

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 date str11 horse str1 racecode str9 course int spdrat float(ddate racetyperuns racetyperunsALL racetyperuns_invert) double maxTFRALL float wanted
"07/07/2022" "Mill Stream" "f" "Doncaster"  82 22833  0 11 11   .  .
"29/07/2022" "Mill Stream" "f" "Newmarket"  97 22855  1 11 10  82 11
"17/08/2022" "Mill Stream" "f" "York"       93 22874  2 11  9  97 10
"27/05/2023" "Mill Stream" "f" "Haydock"    35 23157  3 11  8  97 10
"17/06/2023" "Mill Stream" "f" "York"      107 23178  4 11  7  97 10
"13/07/2023" "Mill Stream" "f" "Newmarket" 114 23204  5 11  6 107  7
"09/09/2023" "Mill Stream" "f" "Haydock"   107 23262  6 11  5 114  6
"21/10/2023" "Mill Stream" "f" "Ascot"      92 23304  7 11  4 114  6
"18/04/2024" "Mill Stream" "f" "Newmarket" 115 23484  8 11  3 114  6
"15/05/2024" "Mill Stream" "f" "York"      120 23511  9 11  2 115  3
"22/06/2024" "Mill Stream" "f" "Ascot"     114 23549 10 11  1 120  2
"13/07/2024" "Mill Stream" "f" "Newmarket"  78 23570 11 11  0 120  2
end
format %td ddate

*DATE
gen ddate = daily(date, "DMY")
format ddate %td

bysort horse racecode (ddate): gen racetyperuns = _n-1
bysort horse racecode (ddate): gen racetyperunsALL = _N-1
gen racetyperuns_invert = racetyperunsALL - racetyperuns

sort horse ddate
rangestat (max) maxTFRALL = spdrat, int(ddate -3650 -1) by (horse racecode)

[Hans Schubert]

Found a solution using the vlookup command

vlookup maxTFRALL, gen (wanted1) key(spdrat) value(racetyperuns_invert)