ICAO code pairing

[Davide Spano]

Good morning,

I am guessing, also if it is not strictly necessary, how it would be possible to pair an ICAO airports codes database (presumably global) to a regional airlines routes database classified by ICAO codes.
In my mind the answer is to build on a loop command, but I can not figure out how define commands inside the loop, mostly because I should compare several thousand of records with a second database with aproximately hundred thousand records (globally) of ICAO takeoff-landing surfaces (heliports, private, military, public/commercial airports).
Any hint?
Many thanks to whom would provide any suggestion, and a big sorry as I a really newbie with Stata

[Clyde Schechter]

It's really not at all clear what you mean by "pair an ICAO airport codes database to a regional airlines root database..." I can think of several things this might mean. I can't think of any that would require writing a loop, though. Moreover, whatever your goal is, even assuming somebody else understands it as written, I doubt anyone can help you without having example data to work with.

Please post back with a fuller explanation of what you want. Show example data from the data sets you wish to pair, and use the -dataex- command to do that so that whoever responds to you will be able to develop and test code properly. Also give either a clear explanation of what the result you wish to achieve looks like, or, better, work it out by hand for a small subset of your data and show it.

If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Davide Spano]

Hi Clyde, thanks a lot for your answer, it helped me to better figure out my objective at least.
I never heard about dataex package, and I will check it for sure.
But, in my mynd initially I was wondering about some automation based on:

1. A DB with more than 16k one lag route between pairs of airports, for 2 years, so aproximately 32K routes and airport pairs
   

   tra_meas	airp_pr	time_period	obs_value	Dep_C	Arr_C	Dep_airp	Arr_airp
   PAS_CRD_ARR	AT_LOWG_AT_LOWW	2019	65959	AT	AT	LOWG	LOWW

2. A global airport database with ICAO code, infrastructure name, location and geographic coordinates.

That automation should find for each route in the first DB each ICAO code (2x - arrival and departure) the corresponding ICAO code in the second DB.

But finally, I think I can manage this issue using a simple merge (many to one) operation. I have not performed it yet (I'm working on the secon DB in order to simplify it eliminating what certainly I will not need).
So for now it is fine, as learning from the forum is always somewhat illuminating. So thank a lot as always.

[Clyde Schechter]

Yes, based on this description, you can accomplish this with two -merge m:1- operations, one keyed on the code for the departure airport and the other keyed on the code for the arrival airport. You will need to do some variable cloning or renaming to match the name of the variable that has the airport code in the second data base.

Post back if things do not go to plan and you need assistance.