In this video we’re going to cover one final method for verticallly combining SAS data sets.
When you append or concatenate data sets, the observations from each individual data set remain grouped together,
and in the same order in the combined data set.
If you want observations in the combined data set to mix together based on the values of one or more common variables,
you can do this with a process known as interleaving the data sets.
For example, let’s look at the trial data sets again,
but this time instead of an ID variable, each observation is identified by last name.
You want to combine these data sets, with the observations in alphabetical order by last name.
To do this, you can interleave the data sets based on the variable Last.
Interleaving data sets requires only a small modification to the same DATA step you used to concatenate data sets.
But, when interleaving data sets, you add a BY statement and specify one or more BY variables.
SAS uses the values of the BY variables to arrange the observations in the output data set.
Remember that any variables specified in the BY statement must be common to all of your input data sets in order for the DATA step to run.
Also notice the DESCENDING option in the BY statement.
By default SAS arranges observations in the output data set by ascending order of the BY variables.
When a character variable is sorted in ascending order,
A is at the top of the data set and Z is at the bottom of the data set.
When a numeric variable is sorted in ascending order, low numbers are at the top of the data set and high numbers are at the bottom.
If you prefer for SAS to sort your BY variable in descending order, you add the DESCENDING option.
This demonstration shows you how to interleave data sets in SAS.
Once again you have the same three advisees data sets as before.
Each observation includes the students last name, gender, and which academic program they are in.
Let’s start with a DATA step to concatenate these data sets into a new data set called advisees.
Remember that you need to use the RENAME option in the SET statement so that the MHA values appear in the variable program.
You can see that the new data set advisees includes all seven observations,
and that the observations are stacked on top of each other in the same order that the appeared in the data sets you specified in the
SET statement. But now you want the data to be in ascending order by last name.
To do that you could simply run the SORT procedure shown here.
It starts with the SAS keywords PROC SORT, then you specify the data set you want to sort.
In the BY statement, you specify which variable or variables you want the data set sorted by, and finally end the procedure with a RUN statement.
Because you want the observations sorted in ascending order, you simply leave off the descending option in the BY statement.
Let’s run the SORT procedure and view the list report for advisees again.
And now the three separate advisees data sets have been interleaved into a single data set called advisees – sorted by last name.
However, you could have accomplished this same task in an alternate way.
Take a look at this DATA step.
Here you are interleaving the data sets as you create the new data set advisees2 by adding a BY statement.
And this time you want the observations arranged by last name in descending order.
So, you add the descending option.
However, in order for this DATA step to execute properly, the three input data sets must first be sorted by the same by variable,
in this case last name. Let’s run this program and take a look at the list report.
And there is your new data set sorted by last name in descending order.
The choice of which method to use for interleaving data sets is completely up to you.
Just remember that if you interleave by adding a BY statement to your DATA step,
you must first sort all the input data sets by the same variable and in the same order as you specify in the BY statement.
Here is a chance for you to practice interleaving data sets.
As you’ve seen, appending, concatenating, and interleaving can generate similar results.
The main difference is that PROC APPEND overwrites the first data set,
whereas the SET statement in a DATA step can create a new output data set.
So, when is best to use which method?
Well, as always to some extent it’s just a matter of preference.
But, here are some rules of thumb from SAS that are intended to help you choose.
If you’re concerned about speed, PROC APPEND is faster because it does not process any of the observations in the
base equals data set.
However, with the small data sets we use in this course, this speed difference will likely be unnoticeable to you.
If you want to combine a large number of data sets, remember that you are limited to two input data sets per PROC APPEND step.
When you use the SET statement you can concatenate or interleave any number of input data sets in a single DATA step.
Finally, if you’re combining data sets that contain different variables,
PROC APPEND is more limited than using the SET statement in a DATA step.
Remember that PROC APPEND uses all variables in the base equals data set.
If the base equals data set has variables that don’t appear in the data equals data set
PROC APPEND assigns missing values to those variables,
and PROC APPEND drops any variables that are only found in the data equals data set.
However, the SET statement uses ALL variables in ALL input data sets.
Now you’ve learned three different methods for combining data sets together vertically.
When you feel comfortable with this material, please move on to the next video on combining data sets horizontally.