[Clyde Schechter]

Instead of matching firm-years 1-to-1, match each sample firm-year to a mean OROAt* from all comparison firms satisfying the below criteria.

I'm afraid I don't understand what this means. Can you show an example of how you would want this to work?

[Carl-Johan Andersson]

Using a hypothetical example from the turnover event with CompanyID 4439 in year 2010 in the dataex I posted earlier:

The event has a OROABencht1 at c. 8,3%, giving low-high values of 7,47% resp. 9,13% and a digitSNICode of 36.
In a 1-to-1 match it could for example match with CompanyID 14620 in year 2005 (OROABencht1 of 8,58% and digitSNICode of 36) and thus I would compare OROAt* between those two.

What I'm now trying to accomplish is to compare the turnover event (CompanyID 4439, year 2010) with a mean OROAt* of not only CompanyID 14620, year 2005 - but all firm-years that match the criteria. E.g., since CompanyID 14620, year 2004 also matches the criteria, I would like to compute the mean OROAt* of CompanyID 14620, year 2005 and CompanyID 14620, year 2004 (and all other possible matches) and compare that to OROAt* of CompanyID 4439, year 2010.

Let me know if the example wasn't clear enough and I'll provide you with a clarification.

Thanks!

[Clyde Schechter]

So, if I understand you correctly, for each turnover event you want to calculate the mean value of OROABencht1 for all of the observations (regardless of SIC code or DataYearFiscal, or anyhthing else) where OROABencht1 falls between 90% and 110% of the current observation's value. That's a direct application of -rangestat- (which you already have because you needed it in order to use -rangejoin-).

* Example generated by -dataex-. To install: ssc install dataex
clear
input long CompanyID int DataYearFiscal byte digitSNICode double OROA byte(Turnover Forcedvoluntary Externalinternal) double(OROABencht1 ΔOROAt1 ΔOROAt2 ΔOROAt3 ΔOROAt4 ΔOROAt5)
 4439 1997 36                    . 0 . .                    .                      .                     .                    .                     .                     .
 4439 1998 36   .12971378187737384 0 . .                    .                      .                     .                    .                     .                     .
 4439 1999 36   .10354958914580546 0 . .   .12971378187737384    -.10201277313228432   -.25954671171749466   -.2071244418895311   -.17886703227229958                     .
 4439 2000 36  .027701008745089524 0 . .   .10354958914580546    -.23338251898592627   -.18096024915796274  -.15270283954073122                     .                     .
 4439 2001 36  -.12983292984012082 0 . .  .027701008745089524    -.10511166875724681   -.07685425914001527                    .                     .                     .
 4439 2002 36  -.07741066001215728 0 . .  -.12983292984012082     .08067967944519508                     .                    .                     .                     .
 4439 2003 36  -.04915325039492575 1 1 1  -.07741066001215728     .22272353698888614    .24526468099117826    .2070861361194029     .1842730402112826    .16822035497415502
 4439 2004 36   .14531287697672887 0 . .  -.04915325039492575      .2170072713739467    .17882872650217138   .15601563059405107     .1399629453569235     .1318250136018257
 4439 2005 36   .16785402097902097 0 . .   .14531287697672887    -.01563740086948323   -.03845049677760354  -.05450318201473113   -.06264111376982893   -.05540112458892861
 4439 2006 36   .12967547610724564 0 . .   .16785402097902097   -.060991640779895634   -.07704432601702323  -.08518225777212103    -.0779422685912207                     .
 4439 2007 36   .10686238019912533 0 . .   .12967547610724564     -.0388657811452479  -.047003712900345704  -.03976372371944538                     .                     .
 4439 2008 36   .09080969496199774 0 . .   .10686238019912533   -.024190616992225397  -.016950627811325072                    .                     .                     .
 4439 2009 36   .08267176320689994 0 . .   .09080969496199774    -.00089794257419748                     .                    .                     .                     .
 4439 2010 36   .08991175238780026 1 0 0   .08267176320689994    .005212132256435936  -.019725221574862728 .0028022073235805106 -.0035566400596804965    .01218513049042734
 4439 2011 36   .08788389546333587 0 . .   .08991175238780026   -.026965210755763053  -.004437781857319814 -.010796629240580821   .004945141309527015  -.038210230037846155
 4439 2012 36   .06294654163203721 0 . .   .08788389546333587  -.0024099249328554256  -.008768772316116433  .006972998233991404  -.036182373113381766   -.12988539892022796
 4439 2013 36   .08547397053048045 0 . .   .06294654163203721     .01616858151518223    .03191035206529007 -.011245019282083102    -.1049480450889293                     .
 4439 2014 36   .07911512314721944 0 . .   .08547397053048045     .00938292316684683   -.03377244818052634  -.12747547398737252                     .                     .
 4439 2015 36   .09485689369732728 0 . .   .07911512314721944   -.027413600797265333   -.12111662660411152                    .                     .                     .
 4439 2016 36   .05170152234995411 0 . .   .09485689369732728    -.13685839715421935                     .                    .                     .                     .
 4439 2017 36  -.04200150345689208 1 1 0   .05170152234995411                      .                     .                    .                     .                     .
11217 1997 37                    . 0 . .                    .                      .                     .                    .                     .                     .
11217 1998 37  .040901436051318164 0 . .                    .                      .                     .                    .                     .                     .
11217 1999 37  .032060501110426264 0 . .  .040901436051318164   -.009441198438083803  -.044518000514338046  -.03191034665781428  -.014892122526642342    .01927966508510444
11217 2000 37   .03146023761323436 0 . .  .032060501110426264   -.035677065573446146   -.02306941171692238 -.006051187585750441    .02812060002599634    .04938555444911721
11217 2001 37 -.003616564463019881 0 . .   .03146023761323436   -.022469148219730478  -.005450924088558538  .028720863523188242    .04998581794630911   .055595200191809684
11217 2002 37  .008991089393503881 0 . . -.003616564463019881    .029625877987695705    .06379766559944249   .08506262002256335    .09067200226806392    .08616598847831239
11217 2003 37  .026009313524675823 0 . .  .008991089393503881     .05119001174291872     .0724549661660396   .07806434841154017    .07355833462178864   .041763309061945725
11217 2004 37    .0601811011364226 0 . .  .026009313524675823     .05543674203486765    .06104612428036822   .05654011049061669   .024745084930773785   -.06923750542680757
11217 2005 37   .08144605555954347 0 . .    .0601811011364226     .02687433666862144    .02236832287886991 -.009426702680972995   -.10340929303855434 -.0053092368112604565
11217 2006 37   .08705543780504404 0 . .   .08144605555954347    .001103368455749043  -.030691657104093863  -.12467424746167521  -.026574191234381324   .004438846345131586
11217 2007 37   .08254942401529251 0 . .   .08705543780504404    -.03630103934959444   -.13028362970717577   -.0321835734798819  -.001170535900368988                     .
11217 2008 37   .05075439845544961 0 . .   .08254942401529251    -.12577761591742426  -.027677559690130367  .003335477889382543                     .                     .
11217 2009 37 -.043228191902131737 0 . .   .05075439845544961    .004117465869712539    .03513050344922545                    .                     .                     .
11217 2010 37   .05487186432516215 0 . . -.043228191902131737      .1291130938068068                     .                    .                     .                     .
11217 2011 37   .08588490190467506 1 0 0   .05487186432516215   -.002163254978648124  -.028028425956137932  -.02155970090503217 -.0025182226052173073                     .
11217 2012 37   .05270860934651402 0 . .   .08588490190467506   -.059041463535650845   -.05257273848454508  -.03353126018473022                     .                     .
11217 2013 37  .026843438369024215 0 . .   .05270860934651402   -.019396445926384044 -.0003549676265691831                    .                     .                     .
11217 2014 37   .03331216342012998 0 . .  .026843438369024215    .025510203350920625                     .                    .                     .                     .
11217 2015 37   .05235364171994484 1 1 1   .03331216342012998     .02344087334172642    .03810638083480089                    .                     .                     .
11217 2016 37    .0567530367618564 0 . .   .05235364171994484     .01906490253498603                     .                    .                     .                     .
11217 2017 37   .07141854425493087 0 . .    .0567530367618564                      .                     .                    .                     .                     .
11749 1997 35                    . 0 . .                    .                      .                     .                    .                     .                     .
11749 1998 35 -.023100936524453695 0 . .                    .                      .                     .                    .                     .                     .
11749 1999 35   .06459054209919261 0 . . -.023100936524453695     .12860591886371223    .11888556655565906   .12066310489866207    .10724017670607132                     .
11749 2000 35   .10550498233925854 0 . .   .06459054209919261    .031194087932012754   .032971626275015764  .019548698082425015                     .                     .
11749 2001 35   .09578463003120537 0 . .   .10550498233925854   -.007942813965050158  -.021365742157640907                    .                     .                     .
11749 2002 35   .09756216837420838 0 . .   .09578463003120537    -.01164538984958774                     .                    .                     .                     .
11749 2003 35   .08413924018161763 1 0 0   .09756216837420838    .027389656764810505    .05585132489637447   .05579974889759513    .05984083348573367    .06479133144232914
11749 2004 35   .12495182513901888 0 . .   .08413924018161763     .06927425308896522    .06922267709018588   .07326376167832442    .07821425963491989  -.028774196916572584
11749 2005 35   .15341349327058285 0 . .   .12495182513901888     .02841009213278463    .03245117672092317  .037401674677518634   -.06958678187397384    .04274395061321086
11749 2006 35    .1533619172718035 0 . .   .15341349327058285    .003989508589359203    .00894000654595467   -.0980484500055378   .014282282481646896   .017870730979743094
11749 2007 35   .15740300185994205 0 . .    .1533619172718035    .008991582544734006   -.09799687400675847  .014333858480426231    .01792230697852243   -.03211355940472364
11749 2008 35   .16235349981653752 0 . .   .15740300185994205      -.102037958594897   .010292773892287693   .01388122239038389   -.03615464399286218  -.051571575028465835
11749 2009 35  .055365043265045044 0 . .   .16235349981653752    .005342275935692226   .008930724433788423  -.04110514194945765    -.0565220729850613   -.05686125432062958
11749 2010 35   .16769577575222974 0 . .  .055365043265045044      .1159191809852809    .06588331460203482   .05046638356643117   .050127202230862894   .029680094475495633
11749 2011 35   .17128422425032594 0 . .   .16769577575222974   -.046447417885149875   -.06186434892075353 -.062203530256321804   -.08265063801168906                     .
11749 2012 35   .12124835786707987 0 . .   .17128422425032594    -.06545279741884973     -.065791978754418  -.08623908650978526                     .                     .
11749 2013 35   .10583142683147621 0 . .   .12124835786707987    -.01575611237117193   -.03620322012653919                    .                     .                     .
11749 2014 35   .10549224549590794 0 . .   .10583142683147621   -.020786289090935536                     .                    .                     .                     .
11749 2015 35   .08504513774054068 1 1 0   .10549224549590794    -.01435056012096908 -.0055005880656577855                    .                     .                     .
11749 2016 35   .09114168537493886 0 . .   .08504513774054068    .014946519689709475                     .                    .                     .                     .
11749 2017 35   .09999165743025015 0 . .   .09114168537493886                      .                     .                    .                     .                     .
12368 1997 24                    . 0 . .                    .                      .                     .                    .                     .                     .
12368 1998 24   .08920161233293261 0 . .                    .                      .                     .                    .                     .                     .
12368 1999 24   .08363849795210085 0 . .   .08920161233293261     .03441657247238107   .023762077909489462   -.0060181085684647                     .                     .
12368 2000 24   .12361818480531368 0 . .   .08363849795210085    .029325192290321225 -.0004549941876329372                    .                     .                     .
12368 2001 24   .11296369024242207 0 . .   .12361818480531368    -.04043468104084577                     .                    .                     .                     .
12368 2002 24   .08318350376446791 1 0 0   .11296369024242207    -.04306698316209534   -.06389330759031134  -.05434671259222728   -.05386740271099181   -.04718012827033796
12368 2003 24   .06989670708032673 0 . .   .08318350376446791   -.034113121112357175  -.024566526114273117 -.024087216233037646    -.0173999417923838                     .
12368 2004 24  .049070382652110736 0 . .   .06989670708032673    -.01127972943013194  -.010800419548896469 -.004113145108242622                     .                     .
12368 2005 24  .058616977650194795 0 . .  .049070382652110736    .010025904879319529   .016713179319973376                    .                     .                     .
12368 2006 24  .059096287531430265 0 . .  .058616977650194795    .007166584321889317                     .                    .                     .                     .
12368 2007 24   .06578356197208411 1 1 0  .059096287531430265 -.00029587388096490724  .0013750022879044435  .004276616331534248 -.0055565821222064715   .001959509424053027
12368 2008 24   .05880041365046536 0 . .   .06578356197208411   -.005312272152749403 -.0024106581091195983 -.012243856562860318   -.00472776501660082   .008176188961155961
12368 2009 24   .06047128981933471 0 . .   .05880041365046536    .004572490212499156  -.005260708241241564  .002255383305017934   .015159337282774715     .0193667590890758
12368 2010 24   .06337290386296451 0 . .   .06047128981933471   -.006931584410110915  .0005845071361485835  .013488461113905365    .01769588292020645    .01927098088690042
12368 2011 24  .053539705409223794 0 . .   .06337290386296451  -.0023171069074812214    .01058684707027556  .014794268876576644   .016369366843270616                     .
12368 2012 24   .06105579695548329 0 . .  .053539705409223794     .02042004552401628   .024627467330317364  .026202565297011336                     .                     .
12368 2013 24   .07395975093324007 0 . .   .06105579695548329    .017111375784057865   .018686473750751838                    .                     .                     .
12368 2014 24   .07816717273954116 0 . .   .07395975093324007    .005782519772995057                     .                    .                     .                     .
12368 2015 24   .07974227070623513 1 1 0   .07816717273954116    .008121646728602591                     .                    .                     .                     .
12368 2016 24   .08628881946814375 0 . .   .07974227070623513                      .                     .                    .                     .                     .
14620 1997 36                    . 0 . .                    .                      .                     .                    .                     .                     .
14620 1998 36   .08824711200401808 0 . .                    .                      .                     .                    .                     .                     .
14620 1999 36   .08649401481588205 0 . .   .08824711200401808    .007522326044093239  -.016118894244621138 -.002781189317220198                     .                     .
14620 2000 36   .09576943804811132 0 . .   .08649401481588205   -.014365797056485108 -.0010280921290841682                    .                     .                     .
14620 2001 36   .07212821775939694 0 . .   .09576943804811132   -.010303515361313437                     .                    .                     .                     .
14620 2002 36   .08546592268679788 1 0 0   .07212821775939694     .01314055916512076   .013658768790948392  .018273746688369036   -.01848108483466524  -.002028746154982039
14620 2003 36    .0852687769245177 0 . .   .08546592268679788   .0003210638635474522   .004936041760968096  -.03181878976206618  -.015366451082382979   -.06473630051064148
14620 2004 36   .08578698655034533 0 . .    .0852687769245177    .005133187523248275     -.031621643999786   -.0151693053201028    -.0645391547483613  -.011853886644523706
14620 2005 36   .09040196444776598 0 . .   .08578698655034533    -.03213985362561363   -.01568751494593043  -.06505736437418894  -.012372096270351338   .003667728977331572
14620 2006 36    .0536471329247317 0 . .   .09040196444776598   -.020302492843351075   -.06967234227160958  -.01698707416777198 -.0009472489200890721   -.04872679680858805
14620 2007 36    .0700994716044149 0 . .    .0536471329247317    -.03291751074857531   .019767757355262296  .035807582602945205  -.011971965285553776                     .
14620 2008 36  .020729622176156395 0 . .    .0700994716044149    .003315418675579093   .019355243923262003  -.02842430396523698                     .                     .
14620 2009 36     .073414890279994 0 . .  .020729622176156395     .06872509335152051    .02094554546302153                    .                     .                     .
14620 2010 36    .0894547155276769 0 . .     .073414890279994    -.03173972264081607                     .                    .                     .                     .
14620 2011 36  .041675167639177925 1 0 0    .0894547155276769    -.02238569583768947  -.037939325255589504  -.02790289384112015  -.033005737841186385  -.017465641478006957
14620 2012 36   .06706901968998744 0 . .  .041675167639177925    .009840222632909477    .01987665404737883  .014773810047312597   .030313906410492024                     .
14620 2013 36    .0515153902720874 0 . .   .06706901968998744    -.00551719800343068  -.010620042003496914  .004920054359682513                     .                     .
end


gen low = OROABencht1 - 0.1*abs(OROABencht1)
gen high = OROABencht1 + 0.1*abs(OROABencht1)

rangestat (mean) OROABencht1, interval(OROABencht1 low high)
replace OROABencht1_mean = . if Turnover != 1

It is still unclear in which of the many senses of the word you wish to "compare" this result with OROABencht1, but presumably that part you can work out once you have the mean value calculated.

By the way, this code includes the turnover observation itself in the calculation of the mean, since necessarily it's OROABencht1 is within 10% of itself! If you want to exclude that, add the -excludeself- option to the -rangestat- command.

[Carl-Johan Andersson]

Thanks!

As you say, I can work out the comparison part myself.

I see now that I wasn't clear about how to handle the SIC code. Ideally, I would want the matching to work the same way as before. I.e. still taking 2-digit SNI code into account. If no matches, using 1-digit and if still no matches, only using the filter.

Is it possible to condition rangestat in a way that accounts for that? As you point out it should still be regardless of DataYearFiscal etc.

[Clyde Schechter]

No, you can't do that within -rangestat-, but -rangejoin- followed by -egen- and some pruning can do it:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long CompanyID int DataYearFiscal byte digitSNICode double OROA byte(Turnover Forcedvoluntary Externalinternal) double(OROABencht1 ΔOROAt1 ΔOROAt2 ΔOROAt3 ΔOROAt4 ΔOROAt5)
 4439 1997 36                    . 0 . .                    .                      .                     .                    .                     .                     .
 4439 1998 36   .12971378187737384 0 . .                    .                      .                     .                    .                     .                     .
 4439 1999 36   .10354958914580546 0 . .   .12971378187737384    -.10201277313228432   -.25954671171749466   -.2071244418895311   -.17886703227229958                     .
 4439 2000 36  .027701008745089524 0 . .   .10354958914580546    -.23338251898592627   -.18096024915796274  -.15270283954073122                     .                     .
 4439 2001 36  -.12983292984012082 0 . .  .027701008745089524    -.10511166875724681   -.07685425914001527                    .                     .                     .
 4439 2002 36  -.07741066001215728 0 . .  -.12983292984012082     .08067967944519508                     .                    .                     .                     .
 4439 2003 36  -.04915325039492575 1 1 1  -.07741066001215728     .22272353698888614    .24526468099117826    .2070861361194029     .1842730402112826    .16822035497415502
 4439 2004 36   .14531287697672887 0 . .  -.04915325039492575      .2170072713739467    .17882872650217138   .15601563059405107     .1399629453569235     .1318250136018257
 4439 2005 36   .16785402097902097 0 . .   .14531287697672887    -.01563740086948323   -.03845049677760354  -.05450318201473113   -.06264111376982893   -.05540112458892861
 4439 2006 36   .12967547610724564 0 . .   .16785402097902097   -.060991640779895634   -.07704432601702323  -.08518225777212103    -.0779422685912207                     .
 4439 2007 36   .10686238019912533 0 . .   .12967547610724564     -.0388657811452479  -.047003712900345704  -.03976372371944538                     .                     .
 4439 2008 36   .09080969496199774 0 . .   .10686238019912533   -.024190616992225397  -.016950627811325072                    .                     .                     .
 4439 2009 36   .08267176320689994 0 . .   .09080969496199774    -.00089794257419748                     .                    .                     .                     .
 4439 2010 36   .08991175238780026 1 0 0   .08267176320689994    .005212132256435936  -.019725221574862728 .0028022073235805106 -.0035566400596804965    .01218513049042734
 4439 2011 36   .08788389546333587 0 . .   .08991175238780026   -.026965210755763053  -.004437781857319814 -.010796629240580821   .004945141309527015  -.038210230037846155
 4439 2012 36   .06294654163203721 0 . .   .08788389546333587  -.0024099249328554256  -.008768772316116433  .006972998233991404  -.036182373113381766   -.12988539892022796
 4439 2013 36   .08547397053048045 0 . .   .06294654163203721     .01616858151518223    .03191035206529007 -.011245019282083102    -.1049480450889293                     .
 4439 2014 36   .07911512314721944 0 . .   .08547397053048045     .00938292316684683   -.03377244818052634  -.12747547398737252                     .                     .
 4439 2015 36   .09485689369732728 0 . .   .07911512314721944   -.027413600797265333   -.12111662660411152                    .                     .                     .
 4439 2016 36   .05170152234995411 0 . .   .09485689369732728    -.13685839715421935                     .                    .                     .                     .
 4439 2017 36  -.04200150345689208 1 1 0   .05170152234995411                      .                     .                    .                     .                     .
11217 1997 37                    . 0 . .                    .                      .                     .                    .                     .                     .
11217 1998 37  .040901436051318164 0 . .                    .                      .                     .                    .                     .                     .
11217 1999 37  .032060501110426264 0 . .  .040901436051318164   -.009441198438083803  -.044518000514338046  -.03191034665781428  -.014892122526642342    .01927966508510444
11217 2000 37   .03146023761323436 0 . .  .032060501110426264   -.035677065573446146   -.02306941171692238 -.006051187585750441    .02812060002599634    .04938555444911721
11217 2001 37 -.003616564463019881 0 . .   .03146023761323436   -.022469148219730478  -.005450924088558538  .028720863523188242    .04998581794630911   .055595200191809684
11217 2002 37  .008991089393503881 0 . . -.003616564463019881    .029625877987695705    .06379766559944249   .08506262002256335    .09067200226806392    .08616598847831239
11217 2003 37  .026009313524675823 0 . .  .008991089393503881     .05119001174291872     .0724549661660396   .07806434841154017    .07355833462178864   .041763309061945725
11217 2004 37    .0601811011364226 0 . .  .026009313524675823     .05543674203486765    .06104612428036822   .05654011049061669   .024745084930773785   -.06923750542680757
11217 2005 37   .08144605555954347 0 . .    .0601811011364226     .02687433666862144    .02236832287886991 -.009426702680972995   -.10340929303855434 -.0053092368112604565
11217 2006 37   .08705543780504404 0 . .   .08144605555954347    .001103368455749043  -.030691657104093863  -.12467424746167521  -.026574191234381324   .004438846345131586
11217 2007 37   .08254942401529251 0 . .   .08705543780504404    -.03630103934959444   -.13028362970717577   -.0321835734798819  -.001170535900368988                     .
11217 2008 37   .05075439845544961 0 . .   .08254942401529251    -.12577761591742426  -.027677559690130367  .003335477889382543                     .                     .
11217 2009 37 -.043228191902131737 0 . .   .05075439845544961    .004117465869712539    .03513050344922545                    .                     .                     .
11217 2010 37   .05487186432516215 0 . . -.043228191902131737      .1291130938068068                     .                    .                     .                     .
11217 2011 37   .08588490190467506 1 0 0   .05487186432516215   -.002163254978648124  -.028028425956137932  -.02155970090503217 -.0025182226052173073                     .
11217 2012 37   .05270860934651402 0 . .   .08588490190467506   -.059041463535650845   -.05257273848454508  -.03353126018473022                     .                     .
11217 2013 37  .026843438369024215 0 . .   .05270860934651402   -.019396445926384044 -.0003549676265691831                    .                     .                     .
11217 2014 37   .03331216342012998 0 . .  .026843438369024215    .025510203350920625                     .                    .                     .                     .
11217 2015 37   .05235364171994484 1 1 1   .03331216342012998     .02344087334172642    .03810638083480089                    .                     .                     .
11217 2016 37    .0567530367618564 0 . .   .05235364171994484     .01906490253498603                     .                    .                     .                     .
11217 2017 37   .07141854425493087 0 . .    .0567530367618564                      .                     .                    .                     .                     .
11749 1997 35                    . 0 . .                    .                      .                     .                    .                     .                     .
11749 1998 35 -.023100936524453695 0 . .                    .                      .                     .                    .                     .                     .
11749 1999 35   .06459054209919261 0 . . -.023100936524453695     .12860591886371223    .11888556655565906   .12066310489866207    .10724017670607132                     .
11749 2000 35   .10550498233925854 0 . .   .06459054209919261    .031194087932012754   .032971626275015764  .019548698082425015                     .                     .
11749 2001 35   .09578463003120537 0 . .   .10550498233925854   -.007942813965050158  -.021365742157640907                    .                     .                     .
11749 2002 35   .09756216837420838 0 . .   .09578463003120537    -.01164538984958774                     .                    .                     .                     .
11749 2003 35   .08413924018161763 1 0 0   .09756216837420838    .027389656764810505    .05585132489637447   .05579974889759513    .05984083348573367    .06479133144232914
11749 2004 35   .12495182513901888 0 . .   .08413924018161763     .06927425308896522    .06922267709018588   .07326376167832442    .07821425963491989  -.028774196916572584
11749 2005 35   .15341349327058285 0 . .   .12495182513901888     .02841009213278463    .03245117672092317  .037401674677518634   -.06958678187397384    .04274395061321086
11749 2006 35    .1533619172718035 0 . .   .15341349327058285    .003989508589359203    .00894000654595467   -.0980484500055378   .014282282481646896   .017870730979743094
11749 2007 35   .15740300185994205 0 . .    .1533619172718035    .008991582544734006   -.09799687400675847  .014333858480426231    .01792230697852243   -.03211355940472364
11749 2008 35   .16235349981653752 0 . .   .15740300185994205      -.102037958594897   .010292773892287693   .01388122239038389   -.03615464399286218  -.051571575028465835
11749 2009 35  .055365043265045044 0 . .   .16235349981653752    .005342275935692226   .008930724433788423  -.04110514194945765    -.0565220729850613   -.05686125432062958
11749 2010 35   .16769577575222974 0 . .  .055365043265045044      .1159191809852809    .06588331460203482   .05046638356643117   .050127202230862894   .029680094475495633
11749 2011 35   .17128422425032594 0 . .   .16769577575222974   -.046447417885149875   -.06186434892075353 -.062203530256321804   -.08265063801168906                     .
11749 2012 35   .12124835786707987 0 . .   .17128422425032594    -.06545279741884973     -.065791978754418  -.08623908650978526                     .                     .
11749 2013 35   .10583142683147621 0 . .   .12124835786707987    -.01575611237117193   -.03620322012653919                    .                     .                     .
11749 2014 35   .10549224549590794 0 . .   .10583142683147621   -.020786289090935536                     .                    .                     .                     .
11749 2015 35   .08504513774054068 1 1 0   .10549224549590794    -.01435056012096908 -.0055005880656577855                    .                     .                     .
11749 2016 35   .09114168537493886 0 . .   .08504513774054068    .014946519689709475                     .                    .                     .                     .
11749 2017 35   .09999165743025015 0 . .   .09114168537493886                      .                     .                    .                     .                     .
12368 1997 24                    . 0 . .                    .                      .                     .                    .                     .                     .
12368 1998 24   .08920161233293261 0 . .                    .                      .                     .                    .                     .                     .
12368 1999 24   .08363849795210085 0 . .   .08920161233293261     .03441657247238107   .023762077909489462   -.0060181085684647                     .                     .
12368 2000 24   .12361818480531368 0 . .   .08363849795210085    .029325192290321225 -.0004549941876329372                    .                     .                     .
12368 2001 24   .11296369024242207 0 . .   .12361818480531368    -.04043468104084577                     .                    .                     .                     .
12368 2002 24   .08318350376446791 1 0 0   .11296369024242207    -.04306698316209534   -.06389330759031134  -.05434671259222728   -.05386740271099181   -.04718012827033796
12368 2003 24   .06989670708032673 0 . .   .08318350376446791   -.034113121112357175  -.024566526114273117 -.024087216233037646    -.0173999417923838                     .
12368 2004 24  .049070382652110736 0 . .   .06989670708032673    -.01127972943013194  -.010800419548896469 -.004113145108242622                     .                     .
12368 2005 24  .058616977650194795 0 . .  .049070382652110736    .010025904879319529   .016713179319973376                    .                     .                     .
12368 2006 24  .059096287531430265 0 . .  .058616977650194795    .007166584321889317                     .                    .                     .                     .
12368 2007 24   .06578356197208411 1 1 0  .059096287531430265 -.00029587388096490724  .0013750022879044435  .004276616331534248 -.0055565821222064715   .001959509424053027
12368 2008 24   .05880041365046536 0 . .   .06578356197208411   -.005312272152749403 -.0024106581091195983 -.012243856562860318   -.00472776501660082   .008176188961155961
12368 2009 24   .06047128981933471 0 . .   .05880041365046536    .004572490212499156  -.005260708241241564  .002255383305017934   .015159337282774715     .0193667590890758
12368 2010 24   .06337290386296451 0 . .   .06047128981933471   -.006931584410110915  .0005845071361485835  .013488461113905365    .01769588292020645    .01927098088690042
12368 2011 24  .053539705409223794 0 . .   .06337290386296451  -.0023171069074812214    .01058684707027556  .014794268876576644   .016369366843270616                     .
12368 2012 24   .06105579695548329 0 . .  .053539705409223794     .02042004552401628   .024627467330317364  .026202565297011336                     .                     .
12368 2013 24   .07395975093324007 0 . .   .06105579695548329    .017111375784057865   .018686473750751838                    .                     .                     .
12368 2014 24   .07816717273954116 0 . .   .07395975093324007    .005782519772995057                     .                    .                     .                     .
12368 2015 24   .07974227070623513 1 1 0   .07816717273954116    .008121646728602591                     .                    .                     .                     .
12368 2016 24   .08628881946814375 0 . .   .07974227070623513                      .                     .                    .                     .                     .
14620 1997 36                    . 0 . .                    .                      .                     .                    .                     .                     .
14620 1998 36   .08824711200401808 0 . .                    .                      .                     .                    .                     .                     .
14620 1999 36   .08649401481588205 0 . .   .08824711200401808    .007522326044093239  -.016118894244621138 -.002781189317220198                     .                     .
14620 2000 36   .09576943804811132 0 . .   .08649401481588205   -.014365797056485108 -.0010280921290841682                    .                     .                     .
14620 2001 36   .07212821775939694 0 . .   .09576943804811132   -.010303515361313437                     .                    .                     .                     .
14620 2002 36   .08546592268679788 1 0 0   .07212821775939694     .01314055916512076   .013658768790948392  .018273746688369036   -.01848108483466524  -.002028746154982039
14620 2003 36    .0852687769245177 0 . .   .08546592268679788   .0003210638635474522   .004936041760968096  -.03181878976206618  -.015366451082382979   -.06473630051064148
14620 2004 36   .08578698655034533 0 . .    .0852687769245177    .005133187523248275     -.031621643999786   -.0151693053201028    -.0645391547483613  -.011853886644523706
14620 2005 36   .09040196444776598 0 . .   .08578698655034533    -.03213985362561363   -.01568751494593043  -.06505736437418894  -.012372096270351338   .003667728977331572
14620 2006 36    .0536471329247317 0 . .   .09040196444776598   -.020302492843351075   -.06967234227160958  -.01698707416777198 -.0009472489200890721   -.04872679680858805
14620 2007 36    .0700994716044149 0 . .    .0536471329247317    -.03291751074857531   .019767757355262296  .035807582602945205  -.011971965285553776                     .
14620 2008 36  .020729622176156395 0 . .    .0700994716044149    .003315418675579093   .019355243923262003  -.02842430396523698                     .                     .
14620 2009 36     .073414890279994 0 . .  .020729622176156395     .06872509335152051    .02094554546302153                    .                     .                     .
14620 2010 36    .0894547155276769 0 . .     .073414890279994    -.03173972264081607                     .                    .                     .                     .
14620 2011 36  .041675167639177925 1 0 0    .0894547155276769    -.02238569583768947  -.037939325255589504  -.02790289384112015  -.033005737841186385  -.017465641478006957
14620 2012 36   .06706901968998744 0 . .  .041675167639177925    .009840222632909477    .01987665404737883  .014773810047312597   .030313906410492024                     .
14620 2013 36    .0515153902720874 0 . .   .06706901968998744    -.00551719800343068  -.010620042003496914  .004920054359682513                     .                     .
end

//    VERIFY digitSNICode HAS TWO DIGITS
assert length(string(digitSNICode, "%02.0f")) == 2

//    MAKE A COPY OF THE FILE FOR JOINING
tempfile copy
save `copy'

//    ONLY FIND MATCHES FOR THE TURNOVER OBSERVATIONS
keep if Turnover == 1
isid CompanyID DataYearFiscal

//    SET LIMNITS OF MATCHING ON OROABencht1
gen low = OROABencht1 - 0.1*abs(OROABencht1)
gen high = OROABencht1 + 0.1*abs(OROABencht1)


//    DO THE JOIN
rangejoin OROABencht1 low high using `copy', keepusing(CompanyID OROABencht1 digitSNICode)
// IF YOU WANT TO EXCLUDE SELF: drop if CompanyID == CompanyID_U
// IF YOU WANT TO EXCLUDE OTHER TURNOVERS: drop if Turnover_U == 1

//    CALCULATE THE DIGIT MATCH IN SNI CODE
gen byte digits_matching = 0
replace digits_matching = 1 if floor(digitSNICode/10) == floor(digitSNICode_U/10)
replace digits_matching = 2 if digitSNICode == digitSNICode_U


//    KEEP ONLY THOSE MATCHES THAT HAVE THE MOST DIGIT-MATCHING ON SNICode
gsort CompanyID DataYearFiscal -digits_matching
by CompanyID DataYearFiscal: keep if digits_matching == digits_matching[1]

//    NOW CALCULATE MEAN OROABencht1 FOR THE SURVIVING MATCHES
by CompanyID DataYearFiscal: egen mean_OROABencht1_U = mean(OROABencht1_U)

//    RETAIN ONE OBSERVATION PER TURNOVER WITH THE MEAN
by CompanyID DataYearFiscal: keep if _n == 1
drop CompanyID_U digitSNICode_U OROABencht1_U

[Carl-Johan Andersson]

Great, thanks!

1. For the surviving matches I actually want to compute the mean for ΔOROAt* and not OROABencht1. Where do I need to adjust the code?
2. I get the error message "Turnover_U not found" when trying to exclude other turnovers.

[Clyde Schechter]

1. That requires a few adjustments. -rangejoin- needs its -keepusing()- option modified to include those variables. And at that point it becomes simpler to use -collapse- rather than the -egen- commands at the end.

2. Sorry, forgot to include Tunover in the -keepusing()- option of -rangejoin-.

//    VERIFY digitSNICode HAS TWO DIGITS
assert length(string(digitSNICode, "%02.0f")) == 2

//    MAKE A COPY OF THE FILE FOR JOINING
tempfile copy
save `copy'

//    ONLY FIND MATCHES FOR THE TURNOVER OBSERVATIONS
keep if Turnover == 1
isid CompanyID DataYearFiscal

//    SET LIMNITS OF MATCHING ON OROABencht1
gen low = OROABencht1 - 0.1*abs(OROABencht1)
gen high = OROABencht1 + 0.1*abs(OROABencht1)


//    DO THE JOIN
rangejoin OROABencht1 low high using `copy', ///
    keepusing(CompanyID digitSNICode Turnover ΔOROAt*)
drop if CompanyID == CompanyID_U
drop if Turnover_U == 1

//    CALCULATE THE DIGIT MATCH IN SNI CODE
gen byte digits_matching = 0
replace digits_matching = 1 if floor(digitSNICode/10) == floor(digitSNICode_U/10)
replace digits_matching = 2 if digitSNICode == digitSNICode_U


//    KEEP ONLY THOSE MATCHES THAT HAVE THE MOST DIGIT-MATCHING ON SNICode
gsort CompanyID DataYearFiscal -digits_matching
by CompanyID DataYearFiscal: keep if digits_matching == digits_matching[1]

//    NOW CALCULATE MEAN OROABencht1 FOR THE SURVIVING MATCHES
ds CompanyID DataYearFiscal *_U, not
collapse (mean) ΔOROAt*_U (first) `r(varlist)', by(CompanyID DataYearFiscal)
rename *_U mean_*

[Carl-Johan Andersson]

As always, very helpful! Thanks a lot!