Hi,

While I have used Stata for simple analysis for a while, I am starting to need to do more complicated tasks.

I wish to make use of reference tables in a similar way that one would use vlookup in Excel. I have a few things I wish to do with this, but here is a simple example:

In my research group we are asking a child’s country of birth, and the mother and father’s countries of birth. When I designed the REDCap form I made this a complete drop-down list with 197 countries and an “Other” option that unlocks a free-text field and an “Unknown” option. The idea was to avoid multiple spellings of the same country and allow for countries to then be processed into larger groups. It looks like this:


(filename: paeds.dta)

As you can see the countries have appropriate value labels in Stata.

Rather than analyse these on an individual country basis, I have decided to group them according to the system of regions and subregions used by the United Nations Statistical Division.

I have another Stata file with all of the countries listed next to their matching number (as per the value label system above), with their UNSD subregion:


(filename countries.dta)

I would like to write a .do file that generates a new variable in paeds.dta. The .do file would look up the country of birth in countries.dta and enter the appropriate subregion into the new variable. Can anyone tell me how this would be achieved? Or direct me to an explanation (as I am sure this has been addressed).

Kind regards