Fill in missing values across the arrays using sum ARRAY

[smend]

I would like to fill in the missing values across the array by assigning the sum of the previous two columns or the sum of the next two columns.  If a1-a3 are missing, then a3=a4+a5.  a2=a3+a4, a1=a2+a3.

data example;
input a1-a10;
datalines;
. . . 3 4 5 6 7 9 10
. . 3 4 5 6 7 . . .
1 2 3 4 5 6 7 . . .
run;

data WANT;
input a1-a10;
datalines;
17 10 7 3 4 5 6 7 9 10
10 7 3 4 5 6 7 13 20 33
1 2 3 4 5 6 7 13 20 33
run;

a1  a2  a3 a4 a5 a6 a7 a8  a9  a10

17  10   7   3   4   5   6   7   9   10

10    7   3   4   5   6   7  13  20  33

  1    2   3   4   5   6   7  13  20  33

[Astounding]

It seems like this is what you are asking for:

data want;

set have;

array a {10};

do k=1 to 8;

   if a{k) = . then a{k} = sum(a{k+1}, a{k+2});

end;

do k=9 to 10;

   if a{k} . then a{k} = sum{a{k-1}, a{k-2});

end;

drop k;

run;

Even if there's some other variation in the logic that you would like to apply, this should be a good framework to begin.

[ballardw]

---

@smend wrote:

I would like to fill in the missing values across the array by assigning the sum of the previous two columns or the sum of the next two columns.  If a1-a3 are missing, then a3=a4+a5.  a2=a3+a4, a1=a2+a3.

data example;
input a1-a10;
datalines;
. . . 3 4 5 6 7 9 10
. . 3 4 5 6 7 . . .
1 2 3 4 5 6 7 . . .
run;

data WANT;
input a1-a10;
datalines;
17 10 7 3 4 5 6 7 9 10
10 7 3 4 5 6 7 13 20 33
1 2 3 4 5 6 7 13 20 33
run;

a1  a2  a3 a4 a5 a6 a7 a8  a9  a10

17  10   7   3   4   5   6   7   9   10

10    7   3   4   5   6   7  13  20  33

  1    2   3   4   5   6   7  13  20  33

---

This works for your example data:

data want;
   set example;
   array a a1-a10;

   if missing(a1) then do;
      /* find last missing at begining*/
      i=2;
      do while (missing(lastmiss));
         if not missing( a[i]) then lastmiss=i-1;
         i+1;
      end;
      do i= lastmiss to 1 by -1;
         a[i]= sum(a[i+1],a[i+2]);
      end;
   end;
   if missing(a10) then do;
      /* find first missing at end*/
      i=9;
      do while(missing(firstmiss));
         if not missing(a[i]) then firstmiss=i+1;
         i=i-1;
      end;
      do i=firstmiss to 10;
         a[i]=sum(a[i-2],a[i-1]);
      end;
    
   end;
   drop i firstmiss lastmiss;
run;

This will fail if you only have one value at either end (A1 or A10), will not do anything for missing in the middle.

This will fail if there are not 2 sequential values with values.

[novinosrin]

data example;
input a1-a10;
datalines;
. . . 3 4 5 6 7 9 10
. . 3 4 5 6 7 . . .
1 2 3 4 5 6 7 . . .
;
run;
data want;
set example;
array t(*) a1-a10;
array r(*) a10-a1;
j=whichn(coalesce(of t(*)),of t(*));
k=whichn(coalesce(of r(*)),of r(*));
if j>1 then
do i=j-1 by -1 to 1;
t(i)=sum(t(i+1),t(i+2))	;
end;
if k>1 then
do i=k-1 by -1 to 1;
r(i)=sum(r(i+1),r(i+2))	;
end;
keep a:;
run;

[Ksharp · Accepted solution]

There are too many scenario you need to consider.

data example;
input a1-a10;
datalines;
. . . 3 4 5 6 7 9 10
. . 3 4 5 6 7 . . .
1 2 3 4 5 6 7 . . .
;
run;

data want;
 set example;
 array x{*} a:;
if missing(x{1}) then do;
 do i=1 to dim(x);
   if not missing(x{i}) then leave;
 end;
 do j=i-1 to 1 by -1;
   x{j}=x{j+2}+x{j+1};
 end;
end;

d=dim(x);
if missing(x{d}) then do;
  do i=dim(x) to 1 by -1;
   if not missing(x{i}) then leave;
 end;
 do j=i+1 to d;
   x{j}=x{j-2}+x{j-1};
 end;
end;
drop i j d;
run;

[smend]

Thank you for you feedback.