SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Replacing missing values with next and previous values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

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: 7,646

Re: Replacing missing values with next and previous values

Posted in reply to StephanDup

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

 

View solution in original post


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

Re: Replacing missing values with next and previous values

Posted in reply to StephanDup

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

Re: Replacing missing values with next and previous values

Posted in reply to StephanDup

Thank you so much!

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 219 views
  • 1 like
  • 2 in conversation