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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.