Hello,
I have a data set like below:
a b c d e
. . . . .
. 1 2 3 4
. . 2 3 4
0 1 2 3 4
I would like to fill the missing values with the first non-missing value in each row. For each row, if all missing or there is no missing, there is nothing to do. But for all other cases, fill the missing value(s) with the first non-missing value. The result that I am looking for is:
a b c d e
. . . . .
1 1 2 3 4
2 2 2 3 4
0 1 2 3 4
Thanks in advance!
for char values use coalescec
data want;
set have;
array t(*) location1-location4;
do _n_=1 to dim(t);
if missing(t(_n_)) then t(_n_)=coalescec(of t(*));
end;
run;
data want;
set have;
array t(*) a--e;
do _n_=1 to dim(t);
if t(_n_)=. then t(_n_)=coalesce(of t(*));
end;
run;
Untested as i am away from my SAS software
Now tested
data have;
input a b c d e;
datalines;
. . . . .
. 1 2 3 4
. . 2 3 4
0 1 2 3 4
;
data want;
set have;
array t(*) a--e;
do _n_=1 to dim(t);
if t(_n_)=. then t(_n_)=coalesce(of t(*));
end;
run;
Thanks, that is great! I am sure that will help me for other questions! But why my modified code is not working on char values?
Thanks for the quick reply. I still have problems. I actually have the values which are not numeric (i posted using numeric values to make it easy, but i guess i made a wrong decision). The exact data is like this:
location1 location2 location3 location4
(missing) ON NB AB
(missing) (missing) AB ON
(missing) (missing) (missing) (missing)
ON NB AB ON
The result will be:
location1 location2 location3 location4
ON ON NB AB
AB AB AB ON
(missing) (missing) (missing) (missing)
ON NB AB ON
I tried to modify your code by replacing the var names and replacing . to ' ' (i.e. numeric missing to char missing). I got some error message like:
NOTE: Character values have been converted to numeric values at the places given by:
(Line):(Column).
159:39
NOTE: Numeric values have been converted to character values at the places given by:
(Line):(Column).
.
.
.
ght that
for char values use coalescec
data want;
set have;
array t(*) location1-location4;
do _n_=1 to dim(t);
if missing(t(_n_)) then t(_n_)=coalescec(of t(*));
end;
run;
That works! Thanks a lot! I should do more research on that.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.