DATA Step, Macro, Functions and more

Fill missing values with the first non-missing value in a row.

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Fill missing values with the first non-missing value in a row.

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!

 


Accepted Solutions
Solution
‎01-20-2018 01:19 AM
Super User
Posts: 2,078

Re: Fill missing values with the first non-missing value in a row.

[ Edited ]

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;

View solution in original post


All Replies
Super User
Posts: 2,078

Re: Fill missing values with the first non-missing value in a row.

 

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

 

Super User
Posts: 2,078

Re: Fill missing values with the first non-missing value in a row.

Posted in reply to novinosrin

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;
Contributor
Posts: 31

Re: Fill missing values with the first non-missing value in a row.

Posted in reply to novinosrin

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? 

Contributor
Posts: 31

Re: Fill missing values with the first non-missing value in a row.

Posted in reply to novinosrin

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)Smiley SadColumn).
159:39
NOTE: Numeric values have been converted to character values at the places given by:
(Line)Smiley SadColumn).

.

.

.

 

 

ght that 

Solution
‎01-20-2018 01:19 AM
Super User
Posts: 2,078

Re: Fill missing values with the first non-missing value in a row.

[ Edited ]

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;
Contributor
Posts: 31

Re: Fill missing values with the first non-missing value in a row.

Posted in reply to novinosrin

That works! Thanks a lot! I should do more research on that.

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 460 views
  • 1 like
  • 2 in conversation