BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

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
1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

6 REPLIES 6
art297
Opal | Level 21

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;

djbateman
Lapis Lazuli | Level 10

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?

art297
Opal | Level 21

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;

Peter_C
Rhodochrosite | Level 12

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

Ksharp
Super User

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

draeko
Fluorite | Level 6

Hello 

 

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 15138 views
  • 4 likes
  • 5 in conversation