COMBINING DATASETS USING STATA 10.x
If you do not see the menu on the left click here to see it (look for ‘merge’ in the menu)
Combining datasets using Stata is a frequent task in data analysis
You merge when you want to add more variables to an existing dataset (type help merge in the command window for more details)
What you need:
· Both files should be in Stata format
· Both files should have at least one variable in common (id)
Step1. You need to sort the data (both datasets) by the id or ids common to the files you want to merge and save the files. Type (for each dataset in turn)
For example (using a hypothetical data1.dta as “master” file, already open):
To verify the merge type
From Stata help, here are the codes for _merge:
_merge==1 obs. from master data
_merge==2 obs. from only one using dataset
_merge==3 obs. from at least two datasets, master or using
If you want to keep the observations common to both datasets you can drop the rest by typing:
You append when you want to add more cases (type help append for more details).
For a PDF updated version see here:
Matching fuzzy text
Reclink stands for ‘record linkage’. It is a program written by Michael Blasnik to merge imperfect string variables. For example
In dataset 1 you may have
In dataset 2 you may have:
Reclink helps you to merge the two databases by using a matching algorithm for these types of variables.
Since it is a user created program, you may need to install it by typing
Once installed you can type help reclink for details
· As in merge, the merging variables must have the same name: state, university, city, name, etc.
· Both the master and the using files should have an id variable identifying each observation. Note: the name of ids must be different, for example id1 (id master) and id2 (id using).
· Sort both files by the matching (merging) variables
The basic sytax is:
The variable myscore indicates the strength of the match; a perfect match will have a score of 1.
Description (from reclink help pages):
“reclink uses record linkage methods to match observations between two datasets where no perfect key fields exist -- essentially a fuzzy merge. reclink allows for user-defined matching and non-matching weights for each variable and employs a bigram string comparator to assess imperfect string matches.
The master and using datasets must each have a variable that uniquely identifies observations. Two new variables are created, one to hold the matching score (scaled 0-1) and one for the merge variable. In addition, all of the matching variables from the using dataset are brought into the master dataset (with newly prefixed names) to allow for manual review of matches.”
The following links may be of interest for now:
For a list of frequently used Stata commands see the following