Desktop productivity for business analysts and programmers

Replace missing values with previous values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Replace missing values with previous values

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?


Accepted Solutions
Solution
‎10-31-2017 12:32 AM
Super User
Posts: 6,933

Re: Replace missing values with previous values

Posted in reply to StephanDup

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;

View solution in original post


All Replies
Solution
‎10-31-2017 12:32 AM
Super User
Posts: 6,933

Re: Replace missing values with previous values

Posted in reply to StephanDup

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;

Occasional Contributor
Posts: 8

Re: Replace missing values with previous values

Posted in reply to Astounding

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

Super User
Posts: 10,849

Re: Replace missing values with previous values

Posted in reply to StephanDup


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;

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 532 views
  • 0 likes
  • 3 in conversation