## Fill Missing Values with Next Non-Missing Observation

Solved
Regular Contributor
Posts: 242

# Fill Missing Values with Next Non-Missing Observation

I know how to use the RETAIN statement to fill in missing values with the last non-missing value, but is there a way to do it backwards?  That is, can I fill in previous missing values with the next non-missing value?

Example - I am trying to get &quot;Filled Data&quot; from the &quot;Missing Values&quot; column:

Time PointMissing ValuesFilled Data

1

.7
2.7
3.7
4.7
577
6.6
766
855
9.4
1044
1133
1222
1311

Accepted Solutions
Solution
‎11-07-2011 01:30 PM
PROC Star
Posts: 7,850

## Fill Missing Values with Next Non-Missing Observation

One way would be to just presort the data in descending order and apply the same logic.  e.g.,

data have;

input Time_Point Missing_Values;

cards;

1 .

2 .

3 .

4 .

5 7

6 .

7 6

8 5

9 .

10 4

11 3

12 2

13 1

;

proc sort data=have out=want;

by descending Time_Point;

run;

data want;

set want;

retain Filled_Data;

if not missing(Missing_Values) then

Filled_Data=Missing_Values;

run;

proc sort data=want;

by Time_Point;

run;

All Replies
Solution
‎11-07-2011 01:30 PM
PROC Star
Posts: 7,850

## Fill Missing Values with Next Non-Missing Observation

One way would be to just presort the data in descending order and apply the same logic.  e.g.,

data have;

input Time_Point Missing_Values;

cards;

1 .

2 .

3 .

4 .

5 7

6 .

7 6

8 5

9 .

10 4

11 3

12 2

13 1

;

proc sort data=have out=want;

by descending Time_Point;

run;

data want;

set want;

retain Filled_Data;

if not missing(Missing_Values) then

Filled_Data=Missing_Values;

run;

proc sort data=want;

by Time_Point;

run;

Regular Contributor
Posts: 242

## Fill Missing Values with Next Non-Missing Observation

This answer works great for this scenario.  Once I posted my question, I thought about doing the reverse sort.  However, I was not sure what to do if both the first AND last observations were missing.  I guess re-sort and rerun the same data step?

PROC Star
Posts: 7,850

## Fill Missing Values with Next Non-Missing Observation

If what you described make sense with your data, then you could do it with something like:

data have;

input Time_Point Missing_Values;

cards;

1 .

2 .

3 .

4 .

5 7

6 .

7 6

8 5

9 .

10 4

11 3

12 2

13 .

;

proc sort data=have out=want;

by descending Time_Point;

run;

data want;

set want;

retain Filled_Data;

if not missing(Missing_Values) then

Filled_Data=Missing_Values;

run;

proc sort data=want;

by Time_Point;

run;

data want (drop=last_;

set want;

retain last_Filled_Data;

if not missing(Filled_Data) then

last_Filled_Data=Filled_Data;

else Filled_Data=last_Filled_Data;

run;

Valued Guide
Posts: 2,190

## Fill Missing Values with Next Non-Missing Observation

have you tried walking up and down your data with the POINT= option of the SET statement?

Super User
Posts: 10,400

## Fill Missing Values with Next Non-Missing Observation

It is another method.

```data have;
input Time_Point Missing_Values;
cards;
1 .
2 .
3 .
4 .
5 7
6 .
7 6
8 5
9 .
10 4
11 3
12 2
13 1
;
run;
data want;
do until(missing_values or last);
set have end=last;
if not missing(missing_values) then want_value=missing_values;
end;
do until(missing_values or _last);
set have end=_last;
output;
end;
run;

```

Ksharp

Occasional Contributor
Posts: 6

## Re: Fill Missing Values with Next Non-Missing Observation

Hello

☑ This topic is solved.