Sql Joins

SAS DATASTEP MERGE AND SQL JOINS
A COMPARISON
 
Objective: The objective of this post is to get a reader get a good knowledge of datastep merges and SQL joins and provide a comparison between them. Also towards the end I would specifically outline the differences between these two to let you decide which of the two to use depending on your output requirements.
There are other ways as well to combine SAS datasets like multiple SET statements and hashing but I will be discussing them in a separate post.
Datastep merge:
A datastep merge is one of the most heavily used programming constructs in SAS. It helps us to combine two or more SAS datasets and outputs a single combined dataset containing all the variables from both datasets if not specified otherwise. I will be using small datasets to demonstrate the merging process.
Let us first look at the syntax:
Data dset1;
Merge dset2(in=a) dset3(in=b);
By byvar1 byvar2... ;
If a and b;
Run;
The datasets I will be using are as follows:
data a;
input id name $ ;
datalines;
1 a1
2 a2
3 a3
4 a4
;
run;
data b;
input id new_name $ ;
datalines;
1 b1
2 b2
3 b3
4 b4
;
run;
I will be updating these datasets according to requirements.
So first we will see a small example of merging datasets A and B to make a new dataset C.
data c;
merge a(in=a) b(in=b);
by id;
run;
The ouput is as follows:
        A                 B                     C
+
=
This is the simplest form of merge where there is one to one mapping of all the key variables.
Now we will look at proc sql joins and also compare them with corresponding datastep merge and point out the differences if any.
Proc SQL joins:
A Proc SQL join works same as a datastep merge to combine two or more SAS datasets. There are many types of SQL joins which can be used based on your output requirements.
1) Inner Join
 
An inner join provides only the matching rows from both datasets. Its syntax is:
Proc sql;
      create table company as
            select a.empid, b.name, b.salary
            from employee as a inner join salary as b
            where a.empid=b.emp_id;
Quit;
Here the final output will be all the employees having their salary information in the salary dataset. If the info for a employee is found in any one of the datasets then that employee will not be present in the final table.
1a) Inner Join with datastep:
 
The same inner join can be performed with a datastep merge.
data company;
  merge employee(in=a) salary(rename=(emp_id=empid) in=b);
  by empid;
  if a and b;
run;
We required the rename as datastep cannot perform a merge on id variables with different names.
Actually there is a way to merge by differently named id variables but that method is at from the notion of elegant coding and is just developed as a workaround as you can always rename id variables easily.
OK OK If you require it so much I will discuss the method towards the end.
2) Outer Join (Left)
 
A Left Outer join outputs the matching rows from both datasets as well as it also outputs the non-matching from the left dataset or the dataset specified first in the query. Its syntax is:
Proc sql;
      create table company as
            select a.empid, b.name, b.salary
            from employee as a left outer join salary as b
            where a.empid=b.emp_id;
Quit;
Here the final output will be all the employees whether or not they have their salary information in the salary dataset. If you want to list all the employees irrespective of their salary being updated in the salary dataset then you will use left outer join.
2a) Left outer Join with datastep:
 
The same join can be performed with a datastep merge. We just need to change the IF condition.
data company;
  merge employee(in=a) salary(rename=(emp_id=empid) in=b);
  by empid;
  if a;
run;
3) Outer Join (Right)
A Right Outer join outputs the matching rows from both datasets as well as it also outputs the non-matching from the right dataset or the dataset specified second in the query. Its syntax is:
Proc sql;
      create table company as
            select a.empid, b.name, b.salary
            from employee as a right outer join salary as b
            where a.empid=b.emp_id;
Quit;
Here the final output will be all the employees who have their salary information in the salary dataset as well as the employees who are not currently updated in employee dataset but their salary info was updated. The situation looks silly but in this case you will be using a right outer join.
One point to note here is that we are taking empid from the employee dataset so for these non-matching employees of the salary dataset the empid will be missing which is definitely not desirable. So as a precaution we generally use the COALESCE function in right outer joins.
Proc sql;
      create table company as
            select coalesce(a.empid,b.emp_id), b.name, b.salary
            from employee as a left outer join salary as b
            where a.empid=b.emp_id;
Quit;
This will solve the problem of missing employee ids.
3a) Right Outer Join with datastep:
 
The right outer join can be performed with a datastep merge similarly as the left outer.
data company;
  merge employee(in=a) salary(rename=(emp_id=empid) in=b);
  by empid;
  if b;
run;
4) Full Join
 
A Full join as you have rightly guessed by now outputs the matching rows from both datasets as well as it also outputs the non-matching from both the datasets. Its syntax is:
Proc sql;
      create table company as
            select coalesce(a.empid,b.emp_id), b.name, b.salary
            from employee as a full join salary as b
            where a.empid=b.emp_id;
Quit;
Here the final output will be all the employees who have their salary information either in the salary dataset or the employee dataset.
Here also we use COALESCE function or the same reason as in Right outer join.
4a) Full Join with datastep:
 
The full join can also be performed with a datastep merge. We just need to eliminate the if condition or for better understanding we can keep the condition as IF A OR B;
data company;
  merge employee(in=a) salary(rename=(emp_id=empid) in=b);
  by empid;
  if a or b;
run;
5) Cartesian Product
 
A simplest join in proc sql where the final out is each row of first dataset combined with each row of the second. Its syntax is:
Proc sql;
      create table company as
            select a.empid, b.name, b.salary
            from employee as a , salary as b
            ;
Quit;
Here the final output will be product of all rows from the employee dataset with all rows in the salary dataset.
Practically this looks wrong as everyone’s salary will be everyone others’ salary, perfect Socialism J
But surprisingly this Cartesian product is the basis of all joins. Whenever you specify a join a Cartesian product is done and the output rows are restricted by certain conditions. So knowing this is necessary.
6) Cartesian Product through a datastep
 
This technique is not very intuitive but is asked in a lot of interviews so I am including it here:
data every_combination;
  /* Set one of your data sets, usually the larger data set */
  set one;
  do i=1 to n;
    /* For every observation in the first data set,    */
    /* read in each observation in the second data set */
    set two point=i nobs=n;
    output;
  end;
 run;
This technique creates a product of all rows.
So above is the hint for answering the question in merging SAS datasets with different id variables names. Let’s see whether you can find the answer.
7) Many to many joins
 
These are not a type of join but a property of the data in the tables you are joining. I am including this here as a type of warning. This occurs when both tables have multiple instances of the same key variable. This can lead to unexpected results and the output is different from PROC SQL join to a DATASTEP MERGE in this case as proc sql is Cartesian product based while merge is one to one matching.
You can create a few examples and study the differences this will help you understand the process better.
Below are the some differences between a merge and a join :
MERGE
JOIN
Default is one-to-one “outer-join” using the given order of observations in the source datasets Default is Cartesian Product; joining all rows from one table with all the rows in the other table
Match-merge results by default in a special case of “full-outer-join” Match-join results by default in an inner-join
Outer-joins may be done on multiple source datasets Outer-joins can be done only on two source tables
Need to sort or index the source datasets before the data step match-merge No need to sort or index the source tables before join
Requires variable or variables that are identical on all datasets May join on columns which are named differently
In a one-to-one match-merge, common
variables that are not included in the
match condition (i.e. are not part of the
BY statement): the value from the latter
dataset sometimes overwrites the value
coming from the left-more dataset
Common columns are not overwritten unless
this is specifically managed by the code via
the use of the COALESCE function
Impossible to do a many-to-many merge
due to one-to-one behavior of MERGE
Relatively easy to do a many-to-many join
Fewer advantages when working with
tables stored in database servers since
databases are designed for extensive
matching tools
Most advantageous when working with tables
stored in Database Servers because
databases are designed for SQL processing
Usually more efficient when combining
small datasets but too much overhead on
large unsorted datasets!
More efficient than Merge when dealing with
multiple large datasets (or when combining an
indexed large table with a small table)
 
Conclusion: This paper gives basic information about the SQL merges and joins and is intended to be used as a starter or a reference in this topic.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: