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.
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;
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;
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;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.