BookmarkSubscribeRSS Feed
jackiegreen
Calcite | Level 5

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.

3 REPLIES 3
derekg
Obsidian | Level 7

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;
novinosrin
Tourmaline | Level 20
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;
Tom
Super User Tom
Super User

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;

image.png

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1162 views
  • 0 likes
  • 4 in conversation