Data Manipulation 1.8

Search the site...

Sentry Page Protection

Data Manipulation [8-18]

Merging data sets

Another way to combine data sets is to merge them side-by-side.

Let's take a look at an example.

Data Profile;
Input Student $ Gender $ Grade;
Datalines;
SID0001 M 7
SID0004 F 7
SID0002 F 7
SID0003 M 8
SID0007 M 6
SID0005 F 9
SID0006 F 9
SID0009 M 7
SID0008 M 7
SID0010 F 10
;
Run;

Data Exam;
Input Student $ Result;
Datalines;
SID0009 70
SID0001 78
SID0004 99
SID0002 90
SID0003 81
SID0008 81
SID0005 78
SID0006 66
SID0007 34
SID0010 66
;
Run;

Data set: PROFILE

Data set: EXAM

A tutoring school wants to find out which grade performs the best in the previous semester.

They have the PROFILE and EXAM data set:

The PROFILE data set contains the profile of each student such as gender and grade.

The EXAM data set contains the recent exam results.

In order to analyze the relationship between grade and exam result, you must first combine the two data sets.

Example

Proc Sort Data=Profile;
By Student;
Run;

Proc Sort Data=Exam;
By Student;
Run;

Data Combined;
Merge Profile Exam;
By Student;
Run;

The PROFILE and EXAM data sets are merged into one data set.

The COMBINED data set now contains all of the four variables: Student, Gender, Grade, and Result.

Two Steps Merging Data Sets

When merging the data sets, you can follow the 2 steps below:

Step 1: Sort each of the input data sets by the merging variable(s).

Step 2: Merge the data sets by the merging variable(s).

These will be explained in the next session.

Exercise

Copy and run the CUST and PURCHASE data set from the yellow box below:

Data Cust;
Input ID Zip ;
Datalines;
9000135 14304
9000133 14300
9000134 14301
9000138 14308
9000136 14308
9000137 14304
9000142 14304
9000139 14300
9000140 14301
9000141 14308
;
Run;

Data Purchase;
Input ID Purchase : dollar5.0;
Datalines;
9000135 720
9000133 1002
9000134 569
9000140 890
9000141 780
9000136 109
9000137 30
9000138 4566
9000139 50
9000142 325
;
Run;

A local retail store wants to find out which geographic area responds the best to the marketing campaign.

They have two data sets:

CUST: the data set that contains the customer ID and their address (zip code)
PURCHASE: the data set that contains the customer ID and the recent purchase amount

Merge the two data sets by the customer ID.

Create any data set or variable if needed.

Need some help?

HINT:
Did you include the MERGE and the BY statements in the data step?

SOLUTION:
proc sort data=cust;
by ID;
run;

proc sort data=purchase;
by ID;
run;

data Cust2;
merge cust purchase;
by id;
run;

Fill out my online form.