topic Re: Replacing missing values with next and previous values in SAS Data Management

Replacing missing values with next and previous values

StephanDup — Tue, 31 Oct 2017 04:42:40 GMT

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?

Re: Replacing missing values with next and previous values

art297 — Tue, 31 Oct 2017 05:14:17 GMT

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

Re: Replacing missing values with next and previous values

StephanDup — Tue, 31 Oct 2017 08:58:47 GMT

Thank you so much!