Advanced Data Manipulation 2 (temp)

Search the site...

Sentry Page Protection

Advanced Data Manipulation [2-6]

In the data manipulation module, we have learned how to concatenate and merge data sets.

There is also another way to combine data sets.

Let's look at an example.

Interleaving Data Sets

When interleaving data sets, you are inserting the observations from each data set in the order of the by-group variable.

Let's look at an example.

data inds1;
input ID $ Score ;
datalines;
101 88
103 99
105 60
;
run;

data inds2;
input ID $ Score ;
datalines;
102 58
104 72
106 91
;
run;

The code above creates two data sets:

INDS1
INDS2

Each data set contains two columns and three observations:

The INDS1 data set contains ID 101, 103 and 105:

The INDS2 data set contains ID 102, 104 and 106:

You can interleave these two data sets by using the SET statement and the BY statement.

Example

data inds;
set inds1 inds2;
by ID;
run;

This is a very simple data step with a SET statement on the INDS1 and INDS2 data sets.

There is also a BY statement on the ID variable:

When executing the step, SAS inserts the observations into the output data set based on the order of the ID column.

In the first data step iteration, SAS inserts the observation with ID 101:

In the second data step iteration, SAS inserts the observation from the next by-group (i.e. ID 102).

SAS continues until all of the observations are inserted into the output data set:

Programming Tips:

Please note that you can achieve the exact same results by concatenating (SET Statement) and sorting the data sets (Proc Sort). However, doing so is less efficient, especially when you are working with some large data sets.
Since this technique involves a BY statement in the data step, the input data sets must be sorted prior to interleaving them.

Exercise

Copy and run the code from the yellow box below:

data male;
input Name: $10. Sex $ Age Height Weight;
datalines;
Henry M 14 63.5 102.5
Philip M 16 72.0 150.0
Robert M 12 64.8 128.0
Alfred M 14 69.0 112.5
John M 12 59.0 99.5
James M 12 57.3 83.0
Thomas M 11 57.5 85.0
Jeffrey M 13 62.5 84.0
William M 15 66.5 112.0
Ronald M 15 67.0 133.0
;
run;

data female;
input Name: $10. Sex $ Age Height Weight;
datalines;
Janet F 15 62.5 112.5
Jane F 12 59.8 84.5
Louise F 12 56.3 77.0
Barbara F 13 65.3 98.0
Carol F 14 62.8 102.5
Judy F 14 64.3 90.0
Alice F 13 56.5 84.0
Joyce F 11 51.3 50.5
Mary F 15 66.5 112.0
;
run;

The code above creates two data sets:

MALE and
FEMALE

Each data set contains a list of students.

Interleave these two data sets using the NAME variable as the by-group.

Need some help?

HINT:
You must first sort the data sets prior to interleaving them.

SOLUTION:

proc sort data=male; by name; run;
proc sort data=female; by name; run;

data combine;
set male female;
by name;
run;

Fill out my online form.