topic Re: Replace missing values with previous values in SAS Enterprise Guide

Replace missing values with previous values

StephanDup — Wed, 25 Oct 2017 07:57:43 GMT

I have a data set as follows:

Date Var1 Var2 Var3 .... Var3000

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 7

I would like to replace the missing values with the previous values across all 3000 variables. I have successfully done it for one variable with the following code:

data want;
 set have;
 retain temp;
 if not missing(Var1) then temp = Var1;
 else Var1 = temp;
 drop temp;
run;

Can one maybe do this in an array?

Re: Replace missing values with previous values

Astounding — Wed, 25 Oct 2017 08:13:50 GMT

You're thinking along the right lines. It's easier if you define a temporary array. Then the elements are automatically retained, and never become part of the output. For example:

data want;

set have;

array real {3000} var1-var3000;

array latest {3000} _temporary_;

do _n_=1 to 3000;

if real{_n_} > . then latest{_n_} = real{_n_};

else real{_n_} = latest{_n_};

end;

run;

Re: Replace missing values with previous values

StephanDup — Wed, 25 Oct 2017 08:51:51 GMT

Thank you so much. It worked!! I can't tell you how long I struggled with that.

Re: Replace missing values with previous values

Ksharp — Wed, 25 Oct 2017 13:08:05 GMT



data have;
infile cards truncover;
retain id 1;
input Date $   Var1 Var2 Var3  Var3000;
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             7
;
run;

data want;
 update have(obs=0) have;
 by id;
 output;
 drop id;
run;