topic Re: Fill in missing values across the arrays using sum ARRAY in New SAS User

Fill in missing values across the arrays using sum ARRAY

smend — Mon, 14 Jan 2019 23:57:11 GMT

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

Re: Fill in missing values across the arrays using sum ARRAY

Astounding — Tue, 15 Jan 2019 00:15:23 GMT

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.

Re: Fill in missing values across the arrays using sum ARRAY

ballardw — Tue, 15 Jan 2019 00:22:54 GMT

@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.

Re: Fill in missing values across the arrays using sum ARRAY

novinosrin — Tue, 15 Jan 2019 02:54:35 GMT


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;

Re: Fill in missing values across the arrays using sum ARRAY

Ksharp — Tue, 15 Jan 2019 08:04:10 GMT

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;

Re: Fill in missing values across the arrays using sum ARRAY

smend — Tue, 05 Mar 2019 18:51:48 GMT

Thank you for you feedback.