Joining Two Files

by Dr. Chong Ho (Alex) Yu

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;

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;