## Replacing missing values with next and previous values

Solved
Occasional Contributor
Posts: 8

# Replacing missing values with next and previous values

I have a data set as follows:

Date    Var1 Var2 Var3 Var 4 Var 5 .... Var3000

10/25   5        2       1                8

10/24

10/23

10/22

10/21   2       4        2                6                5

10/20

10/19

10/18

10/17   4       5        1       5       7

I would like to replace the missing values with the previous values across all 3000 variables. But I would like to replace then with the next value if there is no previous value (such as Var4 and Var3000). I have successfully replaced the missing values with the previous values with the following code:

``````data want;
set have;
array vars{*} _numeric_; array latest{3000} _temporary_; do i = 1 to dim(vars);   if vars{i} > . then latest{i} = vars{i};   else vars{i} = latest{i}; end;run;``````

How can I also replace missing values with the following values that is not missing? Should I introduce another temporary array?

Accepted Solutions
Solution
‎10-31-2017 04:58 AM
PROC Star
Posts: 8,115

## Re: Replacing missing values with next and previous values

Here is one way:

```data have;
input Date \$   Var1-Var5;
cards;
10/25   5        2       1       .         8
10/24   .        .       .       .         .
10/23   .        .       .       .         .
10/22   .        .       .       .         .
10/21   2       4        2       .         6
10/20   .        .       .       .         .
10/19   .        .       .       .         .
10/18   .        .       .       .         .
10/17   4       5        1       5         7

data want;
set have;
length obs \$4;
obs=put(_n_,z4.);
array vars{*} _numeric_;
array latest{5} _temporary_;
do _n_ = 1 to dim(vars);
if vars{_n_} > . then latest{_n_} = vars{_n_};
else vars{_n_} = latest{_n_};
end;
run;

proc sort data=want;
by descending obs;
run;

data want;
set want;
array vars{*} _numeric_;
array latest{5} _temporary_;
do _n_ = 1 to dim(vars);
if vars{_n_} > . then latest{_n_} = vars{_n_};
else vars{_n_} = latest{_n_};
end;
obs=put(_n_,z4.);
run;

proc sort data=want;
by obs;
run;
```

Art, CEO, AnalystFinder.com

All Replies
Solution
‎10-31-2017 04:58 AM
PROC Star
Posts: 8,115

## Re: Replacing missing values with next and previous values

Here is one way:

```data have;
input Date \$   Var1-Var5;
cards;
10/25   5        2       1       .         8
10/24   .        .       .       .         .
10/23   .        .       .       .         .
10/22   .        .       .       .         .
10/21   2       4        2       .         6
10/20   .        .       .       .         .
10/19   .        .       .       .         .
10/18   .        .       .       .         .
10/17   4       5        1       5         7

data want;
set have;
length obs \$4;
obs=put(_n_,z4.);
array vars{*} _numeric_;
array latest{5} _temporary_;
do _n_ = 1 to dim(vars);
if vars{_n_} > . then latest{_n_} = vars{_n_};
else vars{_n_} = latest{_n_};
end;
run;

proc sort data=want;
by descending obs;
run;

data want;
set want;
array vars{*} _numeric_;
array latest{5} _temporary_;
do _n_ = 1 to dim(vars);
if vars{_n_} > . then latest{_n_} = vars{_n_};
else vars{_n_} = latest{_n_};
end;
obs=put(_n_,z4.);
run;

proc sort data=want;
by obs;
run;
```

Art, CEO, AnalystFinder.com

Occasional Contributor
Posts: 8