Fill in Missing Information

Chong Ho Yu, Ph.D.


Acknowledgement: Special thanks for Dr. Kristina Kupanoff for her input in some part of the code.

Consider this case: You collected data yielded from a repeated measures design, in which the examinees took the same tests several times. However, the data entry person only entered the age of the subject during the first attempt into the database. You would like to fill in the missing cells of the field "age" according to the value of the first instance for each subject. Some subjects took four trials, some took five, and some took even more. It would be easy to fix the problem by hand if there were only two subjects and nine rows. But in reality the dataset might have more than 1000 rows. Again, this situation necessitates automation.

username age attempt score
Alex 29 1 100
Alex   2 98
Alex   3 97
Alex   4 100
Jody 27 1 99
Jody   2 78
Jody   3 100
Jody   4 97
Jody   5 96

The following macro function is very simple yet it accomplishes this purpose. Let's name the preceding table as data "one" and all manipulation is performed in dataset "two." The lag function inherits the previous value in the field "age" and puts it into the next row and a new variable named "temp." In the next row, if "age" is empty, then its value will be replaced with the one in "temp," which is copied from the previous "age." Please note that the do loop is set to run five times even though you only have four attempts. Actually, the function still works even if you set the loop to run ten times. If you are not sure about the maximum number of attempts, enter a large number in the do loop.


data two ;set one; run;

%macro fillin;

%do i = 1 %to 5;

data two; set two;

temp =lag(age);

if temp NE " " and age = " " then age = temp;



%mend fillin;


The output will look like the following table:

username age age score temp
Alex 29 29 100  
Alex 29   98 29
Alex 29   97 29
Alex 29   100 29
Jody 27 27 99 29
Jody 27   78 27
Jody 27   100 27
Jody 27   97 27
Jody 27   96 27


SAS tips contents

Computer write-ups contents

Simplified Navigation

Table of Contents

Search Engine