In SAS programming, a common task is to combine observations from two or more data sets into a new data set. Using the DATA step, you can combine data sets in several ways, including the following:

Method of Combining	Illustration
One-to-one reading Creates observations that contain all of the variables from each contributing data set. Combines observations based on their relative position in each data set. Statement: SET
Concatenating Appends the observations from one data set to another. Statement: SET
Appending Appending adds the observations in the second data set directly to the end of the original data set. Procedure: APPEND
Interleaving Intersperses observations from two or more data sets, based on one or more common variables. Statements: SET, BY
Match-merging Matches observations from two or more data sets into a single observation in a new data set according to the values of a common variable. Statements: MERGE, BY

This chapter shows you how to combine SAS data sets using one-to-one reading, concatenating, interleaving, and match-merging. When you use the DATA step to combine data sets, you have a high degree of control in creating and manipulating data sets.

In this chapter, you learn to

When you perform one-to-one reading,

Let's look at a simple case of one-to-one reading.

Now let's see how you might use one-to-one reading.

Suppose you have basic patient data (ID, sex, and age) in the data set Clinic.Patients and want to combine it with other patient data (height and weight) for patients under age 60. The height and weight data is stored in the data set Clinic.Measure. Both data sets are sorted by the variable ID.

Notice that Clinic.Patients contains 7 observations in which the patient age is less than 60, and Clinic.Measure contains 6 observations.

To subset observations from the first data set and combine them with observations from the second data set, you can submit the following program:

data clinic.one2one;
   set clinic.patients;
   if age<60;
   set clinic.measure;
run;

The resulting data set, Clinic.One2one, contains 6 observations (the number of observations read from the smallest data set, here Clinic.Measure). The last observation in Clinic.Patients is not read.

When a program concatenates data sets, all of the observations are read from the first data set listed in the SET statement. Then all of the observations are read from the second data set listed, and so on, until all of the listed data sets have been read. The new data set contains all of the variables and observations from all of the input data sets.

Notice that A and C contain a common variable named Num:

The following DATA step creates Clinic.Concat by concatenating Clinic.Therapy1999 and Clinic.Therapy2000.

data clinic.concat;
   set clinic.therapy1999 clinic.therapy2000;
run;

Below is the listing of Clinic.Concat. The first 12 observations were read from Clinic.Therapy1999, and the last 12 observations were read from Clinic.Therapy2000.

Another way to combine SAS data sets is to append one data set to another using the APPEND procedure. Although appending and concatenating are similar, there are some important differences between the two methods. Whereas the DATA step creates an entirely new data set when concatenating, PROC APPEND simply adds the observations of one data set to the end of a "master" (or BASE) data set. SAS does not create a new data set nor does it read the base data set when executing the APPEND procedure.

To append SAS data sets, you specify a BASE= data set, which is the data set to which observations are added and then specify a DATA= data set, which is the data set containing the observations that are added to the base data set. The data set specified with DATA= is the only one of the two data sets that SAS actually reads.

General form of the APPEND procedure:

    PROC APPEND BASE=SAS-data-set

       DATA=SAS-data-set;

    RUN;

where

• BASE= names the data set to which observations are added

• DATA= names the data set containing observations that are added to the base data set

For example, the following PROC APPEND statement appends the observations in data set B to the end of data set A:

The requirements for appending one data set to another are as follows:

Notice that the final data set is the original data set and that no new data set was created.

The following PROC APPEND statement appends the data set Clinic.Append to the base data set Clinic.Therapy. Notice that the two data sets are like-structured data sets: that is, both data sets have the same variable information.

proc append base=Clinic.Therapy data=Clinic.Append;
run;

Below is the listing of Clinic.Therapy. The first 12 observations already existed in Clinic.Therapy, and the last 12 observations were read from Clinic.Append and added to Clinic.Therapy.

In order to use PROC APPEND with data sets that have unmatching variable definitions, you can use the FORCE option in the PROC APPEND statement.

The FORCE option is needed when the DATA= data set contains variables that meet any one of the following criteria:

If the length of a variable is longer in the DATA= data set than in the BASE= data set, SAS truncates values from the DATA= data set to fit them into the length that is specified in the BASE= data set.

If the type of a variable in the DATA= data set is different than in the BASE= data set, SAS replaces all values for the variable in the DATA= data set with missing values and keeps the variable type of the variable specified in the BASE= data set.

If the BASE= data set contains a variable that is not in the DATA= data set, the observations are appended, but the observations from the DATA= data set have a missing value for the variable that was not present in the DATA= data set. The FORCE option is not necessary in this case.

For example:

proc append base=Clinic.Therapy
            data=Clinic.Append force;
run;

If you use a BY statement when you concatenate data sets, the result is interleaving. Interleaving intersperses observations from two or more data sets, based on one or more common variables.

To interleave SAS data sets, specify a list of data set names in the SET statement, and specify one or more BY variables in the BY statement.

General form, basic DATA step for interleaving:

    DATA output-SAS-data-set;

       SET SAS-data-set-1 SAS-data-set-2;

       BY variable(s);

    RUN;

where

• output-SAS-data-set names the data set to be created

• SAS-data-set-1 and SAS-data-set-2 specify the data sets to be read.

• variable(s) specifies one or more variables that are used to interleave observations.

When SAS interleaves data sets, observations in each BY group in each data set in the SET statement are read sequentially, in the order in which the data sets and BY variables are listed, until all observations have been processed. The new data set includes all the variables from all the input data sets, and it contains the total number of observations from all input data sets.

The following DATA step creates Clinic.Interlv by interleaving Clinic.Therapy1999 and Clinic.Therapy2000.

data clinic.interlv;
   set clinic.therapy1999 clinic.therapy2000;
   by month;
run;

Below is the listing of Clinic.Interlv. Notice that, unlike the previous example, observations are interleaved by month instead of being concatenated.

So far in this chapter, you've combined data sets based on the order of the observations in the input data sets. But sometimes you need to combine observations from two or more data sets into a single observation in a new data set according to the values of a common variable. This is called match-merging.

When you match-merge, you use a MERGE statement rather than a SET statement to combine data sets.

General form, basic DATA step for match-merging:

    DATA output-SAS-data-set;

       MERGE SAS-data-set-1 SAS-data-set-2;

       BY <DESCENDING> variable(s);

    RUN;

where

• output-SAS-data-set names the data set to be created.

• SAS-data-set-1 and SAS-data-set-2 specify the data sets to be read.

• variable(s) in the BY statement specifies one or more variables whose values are used to match observations.

• DESCENDING indicates that the input data sets are sorted in descending order (largest to smallest numerically, or reverse alphabetical for character variables) by the variable that is specified. If you have more that one variable in the BY statement, DESCENDING applies only to the variable that immediately follows it.

Generally speaking, during match-merging SAS sequentially checks each observation of each data set to see whether the BY values match, then writes the combined observation to the new data set.

Basic DATA step match-merging produces an output data set that contains values from all observations in all input data sets. (You can add statements and options to select only observations that match for two or more specific input data sets.)

If an input data set doesn't have any observations for a particular value of the same-named variable, then the observation in the output data set contains missing values for the variables that are unique to that input data set.

Suppose you have sorted the data sets Clinic.Demog and Clinic.Visit as follows:

You can then submit this DATA step to create Clinic.Merged by merging Clinic.Demog and Clinic.Visit according to values of the variable ID.

data clinic.merged; 
   merge clinic.demog clinic.visit; 
   by id; 
run;
proc print data=clinic.merged;
run;

Notice that all observations, including unmatched observations and observations that have missing data, are written to the output data set.

The example above illustrates merging two data sets that are sorted in ascending order of the BY variable ID. To sort the data sets in descending order and then merge them, you can submit the following program.

proc sort data=clinic.demog;
   by descending  id;
run;
proc sort data=clinic.visit;
   by descending  id;
run;
data clinic.merged;
   merge clinic.demog clinic.visit;
   by descending  id;
run;
proc print data=clinic.merged;
run;

Notice that you specify the DESCENDING option in the BY statements in both the PROC SORT steps and the DATA step. If you omit the DESCENDING option in the DATA step, you generate error messages about improperly sorted BY variables.

Now the data sets are merged in descending order of the BY variable ID.

To prepare to merge data sets, SAS

If variables that have the same name appear in more than one data set, the variable from the first data set that contains the variable (in the order listed in the MERGE statement) determines the length of the variable.

The illustration above shows match-merging during the compilation phase. After reading the descriptor portions of the data sets Clients and Amounts, SAS

After compiling the DATA step, SAS sequentially match-merges observations by moving the pointers down each observation of each data set and checking to see whether the BY values match.

When the BY value changes in all the input data sets, the PDV is initialized to missing.

The DATA step merge continues to process every observation in each data set until it has processed all observations in all data sets.

By default, all observations that are written to the PDV, including observations that have missing data and no matching BY values, are written to the output data set. (If you specify a subsetting IF statement to select observations, then only those that meet the IF condition are written.)

Suppose you want to match-merge the data sets Clinic.Demog and Clinic.Visit and select only observations that appear in both data sets.

First, you use IN= to create two temporary variables, indemog and invisit. The IN= variable is a temporary variable that is available to program statements during the DATA step, but it is not included in the SAS data set that is being created.

The DATA step that contains the IN= options appears below. The first IN= creates the temporary variable indemog, which is set to 1 when an observation from Clinic.Demog contributes to the current observation. Otherwise, it is set to 0. Likewise, the value of invisit depends on whether Clinic.Visit contributes to an observation or not.

data clinic.merged;
   merge clinic.demog(in=indemog)
         clinic.visit(in=invisit
   rename=(date=BirthDate));
   by id;
run;

Next, to select only observations that appear in both Clinic.Demog and Clinic.Visit, you specify a subsetting IF statement in the DATA step.

In the DATA step below, the subsetting IF statement checks the values of indemog and invisit and continues processing only those observations that meet the condition of the expression. Here the condition is that both Clinic.Demog and Clinic.Visit contribute to the observation. If the condition is met, the new observation is written to Clinic.Merged. Otherwise, the observation is deleted.

data clinic.merged;
   merge clinic.demog( in=indemog
                     rename=(date=BirthDate))
         clinic.visit (in=invisit
                      rename=(date=VisitDate));
   by id;
   if indemog=1 and invisit=1;
run;
proc print data=clinic.merged;
run;

In previous examples, Clinic.Merged contained 12 observations. In the output below, notice that only 10 observations met the condition in the IF expression.

SAS evaluates the expression within an IF statement to produce a result that is either nonzero, zero, or missing. A nonzero or nonmissing result causes the expression to be true; a zero or missing result causes the expression to be false.

Thus, you can specify the subsetting IF statement from the previous example in either of the following ways. The first IF statement checks specifically for a value of 1. The second IF statement checks for a value that is neither missing nor 0 (which for IN= variables is always 1).

if  indemog=1 and  invisit=1;


if  indemog and  invisit;

As you've seen in previous chapters, you can specify the DROP= and KEEP= options wherever you specify a SAS data set. When match-merging, you can specify these options in either the DATA statement or the MERGE statement, depending on whether or not you want to process values of the variables in that DATA step:

When used in the DATA statement, the DROP= option simply drops the variables from the new data set. However, they are still read from the original data set and are available for processing within the DATA step.