SAS: merge statement

1. merge datasets without by statement

Here are the code:

data three

       merge one two;

run;

Just combined in columns one by one. PDV contains all variables from all datasets. Overlay if common variables.

l  All variables are different. no overlay

one Obs num1   char1 1     1       A 2     2       B 3     4       D

two Obs num2   char2 1     2       X 2     3       Y 3     5       V

three Obs num1   char1     num2     char2 1     1       A       2        X 2     2       B     3        Y 3     4       D       5        V

l  There is only one common variable. overlay the same variables. num from two overlays num from one.

one Obs num1   char1 1     1       A 2     2       B 3     4       D

two Obs num     char2 1     2       X 2     3       Y 3     5       V

three Obs      num       char1     char2 1        2         A        X 2        3         B        Y 3        5         D        V

l  There is more than one common variables. Observations of variables from two overlay those from one under identical variable names.

one Obs num1   char 1     1       A 2     2       B 3     4       D

two Obs num     char 1     2       X 2     3       Y

three Obs  num   char 1       2    X 2       3    Y 3       4    D

2. merge datasets without by statement

Here are the code:

data three

       merge one two;

       by num;

run;

Note all data sets should be sorted when by statements used. And There should be only one common variables. Or errors will be there.

one Obs num1   char1 1     1       A 2     2       B 3     4       D

two Obs num     char2 1     2       X 2     3       Y 3     5       V

three Obs num     char1     char2 1     1       A       . 2     2     B       X 3     3       .         Y 4     4       D        . 5     5       .          V

l  handle duplicates

Obs num  char1 1     1      A1 2     1      A2 3     2      B 4     4      D

Obs num     char2 1     1      O1 2     1      O2 3     1      O3 4     2      X1 5     2      X2 6     3      Y

Obs num     char1     char2 1     1       A1     O1 2     1       A2     O2 3     1       A2     O3 4     2       B       X1 5     2       B       X2 6     3         .      Y 7     4       D        .

observations from two overlay those from one. regarding duplicates, the small observations would be duplicated for fill the cells like 'A2'.

3. merge datasets without by statement

Here are the code:

data three

       merge one (IN=in_a) two (IN=in_b);

       by num;

if in_a and in_b;

run;

The IN= value is 1. Here, the values of variable in_a and in_b remain 1 as long as that BY group is still being processed and the value is not reset by programming logic. Otherwise, the variable has a value of 0.

one Obs num1   char1 1     1       A 2     2       B 3     4       D

two Obs num     char2 1     2       X 2     3       Y 3     5       V

three Obs num     char1     char2 1     2     B       X

data three

       merge one (IN=in_a) two (IN=in_b);

       by num;

if in_a=1;

run;

one Obs num1   char1 1     1       A 2     2       B 3     4       D 4    5     E

two Obs num     char2 1     2       X 2     3       Y 3     5       V

three Obs num char1 char2 1 2 B X 2 3 . Y 3 5 E v