Matching two data sets can be done with a “matching” variable. A matching variable is one that is contained in both of the data sets. For example, sales data may contain a customer number which is also found in a customer file that contains the customer’s contact information. One can create a SAS program that merges this two data sets into a third data set using the customer number. If the matching variable only appears once in the data sets then the matching is said to be “one-to-one”. On the other hand if one data set is more of a table whose values will appear several times in the other data set then it is considered to be one-to-many.
The objective of this post is to merge a “one-to-one” data sets and a one-to-many data set. This “one – to – one” merge will consist of merging state level GDP information with the respective states name. The matching variable will be the abbreviated state name which appears in both data sets.
The following set of code reads in a CSV file into the temporary workfile and names it state GDP. It is very important to sort the data by the common variable which in this case is “abbreviation”.
The next line of code reads in a CSV file into the temporary workfile and names it statenames. This file contains the state abbreviation, complete state name, and name of the state capital. Notice that before the data is populated some formatting is done to accomodate the different lengths that the data presents itself in.
Again, the data need to be sorted by the matching variable before the merging can take place: