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?
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
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
Thank you so much!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.