# Joining Two Files

 by Dr. Chong Ho (Alex) Yu

## Equal Join

Problem

There are two files: File A and File B. File A has some observations that File B doesn't have and vice versa. I want to merge two files if and only if the observations occur in both places.

Solution

In a relational database (RDB) this is called an "equal join." You can do an equal join in a RDB by just dragging the field of one table to the identical field in another table, as shown in the above graph. In SAS you do the following:

```/* Assume that in this case you have affiliate ID in both files. */

Data file_a; ...
temp_id = ssn;
proc sort; by id;

Data file_b; ...
temp_id2 = ssn;
proc sort; by id;

Data all; merge file_a file_b; by id;
if temp_id = temp_id2;

/* You may also use the following alternate approach. */
/* This method uses flags in both files */

Data file_a; ...
flag_a = 1;
proc sort; by id;

Data file_b; ...
flag_b = 1;
proc sort; by id;

Data all; merge file_a file_b; by id;
if flag_a = 1 and flag_b = 1;
```

## Left/Right Join

Problem

There are two files: File A and File B. I want to merge two files but keep only the observations in File A.

Solution

In a relational database (RDB) this is called a "left join" or a "right join," depending upon how you arrange the location of the tables. The above graph shows a left join. The join line indicates a "+=" sign rather than a "=". In SAS you do the following:

```/* Assume that in this case you have affiliate ID in both files. */

Data file_a; ...
infile_a = 1;
proc sort; by id;

Data file_b; ...
proc sort; by id;

Data all; merge file_a file_b; by id;
if infile = 1;```
```/* The alternate approach is */

proc sort data=file_a; by id;

proc sort data=file_b; by id;

Data all; merge file_a(IN=A) file_b(IN=B); by id;
if A;
```