Missing values are treated the same as nonmissing values in a merge.

In a one-to-many merge with common variables that are not the BY variable, customers sometimes expect that only the first nonmissing value of the common variable will be written to the Program Data Vector (PDV) and output and that subsequent missing values will be ignored. However, a missing value is a valid value and it will be handled the same way as a nonmissing value.

Consider the following two data sets:

ONE                                TWO
id age score                       id age name
1  11  90                          1  8  Sarah
1   . 100                          2  10 John
1   .  95
2   9  80
2   . 100

Doing a simple merge of these two data sets by ID will result in the following:

 		Obs    id    age    score    name

                  1      1      8      90     Sarah
                  2      1      .     100     Sarah
                  3      1      .      95     Sarah 
                  4      2     10      80     John
                  5      2      .     100     John

   		Obs    id    age    score    name

                  1      1      8      90     Sarah
                  2      1      8     100     Sarah
                  3      1      8      95     Sarah
                  4      2     10      80     John
                  5      2     10     100     John

Below is the additional processing where we create a variable which has the last nonmissing value for the BY group and use that variable to fill in the cases where the value is missing.

data merge2 (drop=tempage);
   merge one two;
   by id;
   retain tempage;
   if first.id then tempage = .;
   if age = . then age = tempage;
   else tempage = age;
run;

Operating System and Release Information

/*Data set one has missing values for age after the first value for each ID*/
data one;
input id age score;
datalines;
1 11 90
1 . 100
1 . 95
2 9 80
2 . 100
;
data two;
input id name $ age;
datalines;
1 Sarah 8
2 John 10
;
run;

/*A regular merge will result in the missing values be written to the output.*/
data merge1;
merge one two;
by id;
run;

proc print;
run;

/*In this merge, we check for missing values and use the previous age for the ID if the*/
/*age is missing.*/

data merge2 (drop=tempage);
merge one  two;
by id;
retain tempage;
if first.id then tempage = .;
if age = . then age = tempage;
else tempage = age;
run;
proc print;
run;

Simple merge:
                   Obs    id    age    score    name

                    1      1      8      90     Sarah
                    2      1      .     100     Sarah
                    3      1      .      95     Sarah
                    4      2     10      80     John
                    5      2      .     100     John

Merge with additional logic:
                   Obs    id    age    score    name

                    1      1      8      90     Sarah
                    2      1      8     100     Sarah
                    3      1      8      95     Sarah
                    4      2     10      80     John
                    5      2     10     100     John