topic Re: Replacing missing values with the values of next and previous years in SAS Programming

Replacing missing values with the values of next and previous years

jackiegreen — Thu, 28 Mar 2019 20:00:20 GMT

Hello,

I have a dataset that looks like this:

ID State1990 State1991 State1992 State1993.... State2010

001 5 . 1 1 6

002 . . 1 3 2

003 . . . 4 5

I would like to replace the missing values with values of previous years. But I would like to replace then with values of next years if there is no value for previous year (such as observations ID002 and ID003). So it should look like this:

ID State1990 State1991 State1992 State1993.... State2010

001 5 5 1 1 6

002 1 1 1 3 2

003 4 4 4 4 5

I have no ideas how to do this.

Re: Replacing missing values with the values of next and previous years

derekg — Thu, 28 Mar 2019 20:23:44 GMT

Maybe something like this?

data d2;
    set d;

    nvars = 21;

    array a{*} state1990-state2010;

    do i=nvars to 1 by -1;
        if missing(a{i}) and i > 1 then a{i} = a{i-1};
        if missing(a{i}) and i < nvars then a{i} = a{i+1};
    end;

    drop nvars i;
run;

Re: Replacing missing values with the values of next and previous years

novinosrin — Thu, 28 Mar 2019 20:40:24 GMT

data have;
input ID    $ State1990 State1991 State1992 State1993  State1994 ;
cards ;
001        5                  .                 1                  1                    6 
002        .                   .                 1                  3                    2 
003        .                   .                 .                   4                    5
;


data want;
set have;
array t(*) state1990-state1994;
do i=1 to dim(t);
if whichn(coalesce(of t(*)),of t(*))>1 and missing(t(i)) then t(i)=coalesce(of t(*));
else if missing(t(i)) then t(i)=t(i-1);
end;
drop i;
run;

Re: Replacing missing values with the values of next and previous years

Tom — Thu, 28 Mar 2019 20:45:46 GMT

Just make two passes over the array of values. Once from left to right and then again from right to left.

data have;
  input id $ State1990-State1994;
cards;
001 5 . 1 1 6
002 . . 1 3 2
003 . . . 4 5
;

data want;
  set have;
  array x state: ;
  do i=2 to dim(x);
    x(i)=coalesce(x(i),x(i-1));
  end;
  do i=dim(x)-1 to 1 by -1;
    x(i)=coalesce(x(i),x(i+1));
  end;
  drop i;
run;