SAS Programming: One – to – One Merge


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.

CODE

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 data consists of per capital GDP for 2010 at the state level.

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:

The following code is the objective of this post.  Using the “merge” and “by” statements the following code merges the two data sets with a one-to-one relationship.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s