DATA Step, Macro, Functions and more

fill up missing values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

fill up missing values

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.


Accepted Solutions
Solution
‎03-01-2016 10:54 AM
Super User
Posts: 10,020

Re: fill up missing values

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;
 

View solution in original post


All Replies
Super User
Posts: 19,770

Re: fill up missing values

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;
Solution
‎03-01-2016 10:54 AM
Super User
Posts: 10,020

Re: fill up missing values

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

Re: fill up missing values

thanks!

Super User
Posts: 5,498

Re: fill up missing values

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.

Respected Advisor
Posts: 3,799

Re: fill up missing values

Posted in reply to Astounding

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.

 

Capture.PNG

 

 

 

Super User
Posts: 5,498

Re: fill up missing values

Posted in reply to data_null__

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   .

 

Respected Advisor
Posts: 3,799

Re: fill up missing values

Posted in reply to Astounding

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

 

Capture.PNG

Occasional Contributor
Posts: 5

Re: fill up missing values

Posted in reply to Astounding

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

Trusted Advisor
Posts: 1,117

Re: fill up missing values

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.

Respected Advisor
Posts: 3,799

Re: fill up missing values


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;

Capture.PNG

Occasional Contributor
Posts: 5

Re: fill up missing values

Posted in reply to data_null__

thanks @data_null__ that worked beautifully!

Super User
Posts: 5,498

Re: fill up missing values

[ Edited ]

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;

☑ This topic is solved.

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

Discussion stats
  • 12 replies
  • 500 views
  • 3 likes
  • 6 in conversation