The SAS Output Delivery System and reporting techniques

Fill Missing Values with Next Non-Missing Observation

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

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 "Filled Data" from the "Missing Values" 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,363

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;

View solution in original post


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

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: 220

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,363

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_Smiley Happy;

  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,175

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: 9,682

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 6720 views
  • 2 likes
  • 5 in conversation