topic Re: fill up missing values in SAS Programming

fill up missing values

madelman — Tue, 01 Mar 2016 03:54:42 GMT

Hi, hopefully this will be a relatively easy question.

I have a data set with the following format

ID status

1 .

1 5

2 .

2 6

etc

would like to get it like this

ID status

1 5

2 6

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

thanks in advance.

Re: fill up missing values

Reeza — Tue, 01 Mar 2016 04:17:27 GMT

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;

Re: fill up missing values

Ksharp — Tue, 01 Mar 2016 07:57:03 GMT

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;

Re: fill up missing values

madelman — Tue, 01 Mar 2016 15:55:00 GMT

thanks!

Re: fill up missing values

Astounding — Tue, 01 Mar 2016 16:40:51 GMT

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.

Re: fill up missing values

data_null__ — Tue, 01 Mar 2016 19:21:36 GMT

@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.

Re: fill up missing values

Astounding — Tue, 01 Mar 2016 19:37:49 GMT

Now try it with a slight variation to the data.

ID Status

1 .

1 5

1 .

2 4

2 .

3 .

3 1

3 .

Re: fill up missing values

data_null__ — Tue, 01 Mar 2016 23:13:55 GMT

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

Re: fill up missing values

madelman — Wed, 02 Mar 2016 04:10:46 GMT

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

Re: fill up missing values

FreelanceReinh — Wed, 02 Mar 2016 09:44:29 GMT

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.

Re: fill up missing values

data_null__ — Wed, 02 Mar 2016 10:54:52 GMT

@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;

Re: fill up missing values

Astounding — Wed, 02 Mar 2016 15:53:41 GMT

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&colon;

data want;

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

by id;

run;

Re: fill up missing values

madelman — Sun, 06 Mar 2016 19:36:47 GMT

thanks @data_null__ that worked beautifully!