topic Fill missing values with the previous or nexy values by id variable in SAS Programming

Fill missing values with the previous or nexy values by id variable

ML86 — Thu, 02 Jun 2022 13:38:37 GMT

Dear community,

I have a problem and I hope you can help me. Image I have the following table

id Var1 Var2 Var3 Var4 Var5 Var6

1 10 . . 0.25 . .

1 . 11 . . 0.3 .

1 . . 41 . . 0.03

2 46 . . 0.02 . .

3 28 . . 0.03 . .

3 . 29 . . 0.02

I want to replace the missing values (.) with the previous or next values by considering the id-variable. That means I want to have the following table

id Var1 Var2 Var3 Var4 Var5 Var6

1 10 11 41 0.25 0.3 0.03

2 46 11 41 0.02 . .

3 28 . 29 0.03 . 0.02

How I can I do this?

Thank you very much and kind regards

Re: Fill missing values with the previous or nexy values by id variable

PaigeMiller — Thu, 02 Jun 2022 13:56:07 GMT

From the data you show, a solution which uses the mean value in place of the missings ought to work, and is very simple to do using PROC STDIZE. Would that work for you?

Re: Fill missing values with the previous or nexy values by id variable

novinosrin — Thu, 02 Jun 2022 14:15:12 GMT

or use an UPDATE by ID to fill, and loop the filled for the count of occurrences of ID-


data have;
input id   Var1   Var2   Var3   Var4   Var5   Var6;
cards;
1   10       .         .           0.25   .         .     

1   10      .          .           0.25  .         .

1   .       11         .           .        0.3     .

1   .       11        .           .         0.3    .

1   .      .           41        .        .        0.03

1   .     .           41        .        .        0.03

1  .      .          41        .        .         0.03

2   46  .         .          0.02     .       .

3   28 .        .           0.03     .        .

3   .     .         29        .          .        0.02
;

data want ;
  do _n_ = 1 by 1 until (last.id ) ;
    update have(obs=0) have ;
    by id ;
  end ;
  do _n_ = 1 to _n_ ;
    output ;
  end ;
run ;

Re: Fill missing values with the previous or nexy values by id variable

Tom — Thu, 02 Jun 2022 14:16:25 GMT

You example seems to result in all observations in a group being exactly the same.

What do you want to happen when the non-missing values are not all the same?

Is there another variable that can be used to insure that the observations are in the right order? A sequence number or date perhaps?

Re: Fill missing values with the previous or nexy values by id variable

mkeintz — Thu, 02 Jun 2022 15:23:37 GMT

@novinosrin

I like the concept of using UPDATE, followed by a do loop of SET/OUTPUT statements. But it can be cleaned a bit:

data want ;
  update have(obs=0) have ;
  by id ;

  if last.id then do until (last.id);
    set have (keep=id);
    by id;
    output;
  end ;
run ;

I should add that this provides a way to specify a variable list in the "keep=" parameter that allows original values to prevail.

Re: Fill missing values with the previous or nexy values by id variable

Ksharp — Fri, 03 Jun 2022 03:11:46 GMT

If your these variables have the same value within the same group ID .

proc sql;
create table want(drop=dummy) as
select id,var1 as dummy,
max(var1) as var1 ,
max(var2) as var2 ,
max(var3) as var3 ,
max(var4) as var4 ,
max(var5) as var5 
 from have 
  group by id;
quit;

Re: Fill missing values with the previous or nexy values by id variable

ML86 — Fri, 03 Jun 2022 05:55:48 GMT

Thank you very much for the suggested solutions.