topic Fill Missing Values with Next Non-Missing Observation in ODS and Base Reporting

Fill Missing Values with Next Non-Missing Observation

djbateman — Mon, 07 Nov 2011 18:04:42 GMT

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 "Filled Data" from the "Missing Values" column:

Time Point	Missing Values	Filled Data
1	.	7
2	.	7
3	.	7
4	.	7
5	7	7
6	.	6
7	6	6
8	5	5
9	.	4
10	4	4
11	3	3
12	2	2
13	1	1

Fill Missing Values with Next Non-Missing Observation

art297 — Mon, 07 Nov 2011 18:30:17 GMT

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;

Fill Missing Values with Next Non-Missing Observation

djbateman — Mon, 07 Nov 2011 18:34:59 GMT

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?

Fill Missing Values with Next Non-Missing Observation

art297 — Mon, 07 Nov 2011 18:47:24 GMT

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;

Fill Missing Values with Next Non-Missing Observation

Ksharp — Thu, 10 Nov 2011 02:25:43 GMT

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

Fill Missing Values with Next Non-Missing Observation

Peter_C — Thu, 10 Nov 2011 11:12:00 GMT

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

Re: Fill Missing Values with Next Non-Missing Observation

draeko — Sun, 30 Apr 2017 22:59:24 GMT

Hello

Do your code works when the dataset is grouped?

I am dealing with this case and so far I can not make your code to worK?

Could you please give me a hand?

Thank you