First One In Gets the Win

Yikes, it's been a while since the last update! So I will try to keep this one short and useful. Most everybody knows there are essentially two ways for tables to be merged in SAS: using the merge statement in the data step and using a join in SQL. Programmers tend to prefer one way over the other, and generally they are interchangeable. However, there are some minor differences that you should keep in mind. One such difference is in how overlapping variables are handled.

Here is a very basic one-to-one merge and its SQL equivalent:

data left;

  do i = 1 to 10;

    output;

  end;

run;



data right;

  do i = 1 to 10;

    output;

  end;

run;



data merged;

  merge left(in=a) right(in=b);

  by i;

run;



proc sql noprint;

  create table joined as 

  select a.*,b.* 

  from left a inner join right b

  on a.i = b.i;

quit;

Now lets add another variable that is the same on both data sets:

data left;

  length overlap $8;

  do i = 1 to 10;

    overlap = 'left';

    output;

  end;

run;



data right;

  length overlap $8;

  do i = 1 to 10;

    overlap = 'right';

    output;

  end;

run;

Now when the two data sets are merged, what value will be in the rows for the overlap variable?

It depends on the order you specify the data sets on the merge statement.

The value comes from the last data set to contribute a record to the merge.

data merged;

  merge left(in=a) right(in=b);

  by i;

run;

The resulting value for overlap will be 'right' because it is the last one named on the merge statement and each row in left has a match in right.

Would you expect it to work the same way in proc SQL? Of course not! You are a SAS programmer. These types of inconsistencies keep you employed.

proc sql noprint;

  create table joined as 

  select a.*,b.* 

  from left a inner join right b

  on a.i = b.i;

quit;

The value for overlap is 'left' in the joined data set. Opposite of how the data step merge works. I like to remember the SQL rule as: First one in gets the win.

How To Get What You Want Out of a Data Step Merge

This Fall my daughter will be going to kindergarden. So like all other hyper-attentive parents we have started introducing her to the concept of homework. The other night I got out her crayons and introduced her to set theory. After about an hour I finally got her to draw her Venn diagrams with the the corresponding SAS data step code for a merge. I was so proud I decided to post it here.

In her drawing data set A is red and B is blue. The shaded area is what's kept.
Disregard the part where she drew herself building a sand castle on the beach. It has nothing to do with Venn diagrams, or SAS data step merging code.


What? You don't believe my five year old daughter drew it? :)

Just a quick refresher: The A and B business refers to the automatic variables that are created when you use the IN= data set option. Essentially the variable A will be "true" whenever that data set contributes an observation to the merge (or join). Also, I don't know why everyone uses A and B-- you can set these variables to anything (left|right, paid|due, etc); but I've always seen A and B so I will stick with convention.

data merged;
  merge someData(in=A) otherData(in=B);
  by someKey;
  if a and (not b); * just keep the observations in A that do not match anything to B;
run;

Saving Steps With SQL

Often we need to create some simple statistics for a set of data and then associate those stats with each observation of the original set. As a simple example
consider a table with only three rows:
N
3
6
4

We want to get the mean of the variable N and stripe it down all the observations:
N Mean
3 4.333
6 4.333
4 4.333

The first way I learned to do this was with a proc summary and a merge. A better way to do it is with proc sql.

Here is a little test data:
data myData;
input x level $;
cards;
11 a
31 a
51 a
2 b
61 a
8 b
21 a
71 a
91 a
4 b
61 a
21 a
5 b
7 b
5 b
31 a
1 b
61 a
8 a
9 b
3 a
2 b
5 a
7 b
7 a
3 b
;
* in that data set we have two variables X and LEVEL. We can get the stats on X for each level by summarizing and merging...;
proc sort data = myData;
by level;
run;

proc summary data = myData;
by level;
var x;
output out= tempStats(drop=_type_ _freq_) mean=mean max=max min=min;
run;

data sumStats;
merge myData tempStats;
by level;
run;

* or better yet, we can collapse the whole thing into one nifty proc sql step!;
proc sql;
create table stats as select *,
min(x) as min,
max(x) as max,
mean(x) as mean
from myData group by level;
quit;