SQL I: Joining Datasets

In a previous posting we talked about joining SAS Datasets using the Data Step.  This time around let’s talk about joining datasets using Proc SQL.  We’ll work through 4 different examples to see how the DATA step and PROC SQL differ and which one works best in which situation.

One-to-One matches

WEIGHTS COLLECTED IN JANUARY 2013  (JAN2013)

ANIMAL	WT1
1	14
2	18
3	20

WEIGHTS COLLECTED IN FEBRUARY 2013 (FEB2013)

ANIMAL	WT2
1	21
2	30
3	37

Data weights_2013;
merge jan2013 feb2013;
by animal;
Run;

Proc sql;
select jan2013.animal, jan2013.wt1, feb2013.wt2
from jan2013, feb2013
where jan2013.animal = feb2013.animal;
Quit;

WEIGHTS_2013

ANIMAL	WT1	WT2
1	14	21
2	18	30
3	20	37

One-to-many matches

WEIGHTS COLLECTED IN JANUARY 2013  (JAN2013)

ANIMAL	WT1
4	21
5	15
6	19

WEIGHTS COLLECTED IN FEBRUARY 2013 (FEB2013)

ANIMAL	WT2
4	38
5	34
5	36
6	41

Data weights_2013;
    merge jan2013 feb2013;
    by animal;
Run;

Proc sql;
    select jan2013.animal, jan2013.wt1, feb2013.wt2
    from jan2013, feb2013
    where jan2013.animal = feb2013.animal;
Quit;

WEIGHTS_2013

ANIMAL	WT1	WT2
4	21	38
5	15	34
5	15	36
6	19	41

Many-to-many matches

WEIGHTS COLLECTED IN JANUARY 2013  (JAN2013)

ANIMAL	WT1
7	14
7	17
8	15

WEIGHTS COLLECTED IN FEBRUARY 2013 (FEB2013)

ANIMAL	WT2
7	19
7	21
8	28

Data weights_2013;
    merge jan2013 feb2013;
    by animal;
Run;

Proc sql;
    select jan2013.animal, jan2013.wt1, feb2013.wt2
        from jan2013, feb2013
        where jan2013.animal = feb2013.animal;
Run;

Data Step Resulting table

ANIMAL	WT1	WT2
7	14	19
7	17	21
8	15	28

 Proc SQL Resulting table

ANIMAL	WT1	WT2
7	14	19
7	14	21
7	17	19
7	17	21
8	15	28

Non-matching

WEIGHTS COLLECTED IN JANUARY 2013  (JAN2013)

ANIMAL	WT1
9	11
10	15
12	16

WEIGHTS COLLECTED IN FEBRUARY 2013 (FEB2013)

ANIMAL	WT2
9	20
12	24
13	30

Data weights_2013;
    merge jan2013 feb2013;
    by animal;
Run;

Proc sql;
    select jan2013.animal, jan2013.wt1, feb2013.wt2
        from jan2013, feb2013
        where jan2013.animal = feb2013.animal;
Run;

Data Step Resulting table

ANIMAL	WT1	WT2
9	11	20
10	15
12	16	24
13		30

 Proc SQL Resulting table

ANIMAL	WT1	WT2
9	11	20
13	16	24