fill up missing values

[madelman]

Hi, hopefully this will be a relatively easy question.

I have a data set with the following format

ID    status

1        . 

1        .

1        . 

1        5

2        .

2        .

2        6

etc

would like to get it like this

ID    status

1        5 

1        5

1        5

1        5

2        6

2        6

2        6

pretty much need to fill "up" the rows instead of down. any thoughts?

thanks in advance.

[Reeza]

If you only have one value per ID, you can use a summary function within SQL to obtain the value. 

See the example below.

proc sql;
create table want as
select *, max(status) as status2
from have
group by ID;
quit;

[Ksharp · Accepted solution]

There are many scenario you need to consider about .

data have;
input ID    status;
cards;
1        . 
1        .
1        . 
1        5
2        .
2        .
2        6
;
run;

data want;
 do until(not missing(status) or last.id );
  set have;
  by id;  
 end;
 temp=status;
 do until(not missing(status) or last.id );
  set have;
  by id;
  _status=temp;output;
 end;
 drop temp status;
run;
 

[madelman]

thanks!

[Astounding]

This works as long as it's always the last observation (per ID) that contains a nonmissing value.  If a nonmissing value comes earlier, you will run into trouble.

[data_null__]

---

@Astounding wrote:

This works as long as it's always the last observation (per ID) that contains a nonmissing value.  If a nonmissing value comes earlier, you will run into trouble.

---

I believe you are wrong about that.

[Astounding]

Now try it with a slight variation to the data.

ID  Status

1    .

1    .

1    5

1    .

2    4

2    .

2    .

3    .

3   1

3   .

3   .

[data_null__]

As expected.  @madelman mentions only "filling-up".

[madelman]

Hi @Astounding, how would you deal with this data? the proc sql solution mentioned above didnt work for my data. thanks for your help.

[FreelanceReinh]

The PROC SQL solution works with Astounding's test data. If it doesn't work with your data (incorrect output? error message in the log?), please post a sample of your data so that we can tell you how to fix the issue.

[data_null__]

---

@madelman wrote:

Hi @Astounding, how would you deal with this data? the proc sql solution mentioned above didnt work for my data. thanks for your help.

---

@madelman Are you now saying that you DO want to fill down too?  I think this modification of @Ksharp program will do.

title;
data have;
   input ID status @@; 
   cards;
1 . 1 . 1 5 1 .
2 4 2 . 2 . 
3 . 3 1 3 . 3 .
4 . 4 . 4 5 4 . 4 4 4 . 4 .
;
run;
proc print;
   run;

data want;
   if 0 then set have;
   do until(not missing(status) or last.id );
      set have;
      by id;  
      if first.id then ltemp=.;
      end;
   temp=status;
   do until(not missing(status) or last.id );
      set have;
      by id;
      _status=coalesce(temp,ltemp);
      output;
      end;
   retain ltemp;
   ltemp=temp;
   drop temp ltemp;
   run;
proc print;
   run;

[madelman]

thanks @data_null__ that worked beautifully!

[Astounding]

First, let me say that KSharp's solution is both elegant and satisfied the original request. I like his code, but wasn't sure if that original request actually illustrated all the conditions in the data that might occur.

To me, the real issue is the structure of the data originally.  For example, will there always be exactly one nonmissing STATUS per ID?  If so, you could code:

data want;

merge have (keep=ID) have (where=(status > .));

by ID;

run;

This also does no harm if an ID has all missing STATUS values.

But this has flaws if there could be more than one nonmissing STATUS per ID.  So the question goes back to STATUS.  Could there be more than one value per ID?  What positions could it occupy?  If there are two, what value should be used?  (Does it matter?)  In particular, what values should be used if the data looks like this?

ID   Status

1         .

1        1

1        .

1        2

1        .

Now there is a need to fill up, fill down, and fill in between.  The programming depends on the intended result.

A better modification due to the likelihood that you have additional variables in the data:

data want;

merge have (drop=status) have (keep=id status where=(status > .));

by id;

run;