DATA Step, Macro, Functions and more

fill out missing value using array linearly

Reply
Frequent Contributor
Posts: 133

fill out missing value using array linearly

I have 13 values, 9 are missing, like this:

0.4835 . 0.7335 . . . 1.184 . . . . . 1.64

/*between 0.4835 and 0.7335, it should be (0.7335-.4835)/2 = .125, so the missing value is 0.4835 + 1*0.125 = 0.6085 */

/*between 1.184 and 0.7335, it should be (1.184 - .07335)/4 = .112625, so the missing value next to 0.7335 is 0.7335 + 1 * .11625 = 0.846125, next is 0.7335 + 2 *.11625=0.95875, etc

between 1.64 and 1.184, it should be (1.64 - 1.184)/6 = 0.076, so the missing value next to 1.184 is 1.184 +1*.076 = 1.26, next one is 1.184 + 2*0.076 = 1.336, next one is 1.184+3*.076=1.412

Final result should be:

0.4835 0.6085 0.7335 0.846125 0.95875 1.071375  1.184 1.26 1.336 1.412 1.488 1.564 1.64

*/

I wrote the code using array, but it gives me the error saying p out of range( ERROR: Array subscript out of range ), I can't figure out why.

data crv5;

input

r1 r2 r3 r4 r5 r6 r7 r8 r9 r10 r11 r12 r13 ;

cards;

0.4835 . 0.7335 . . . 1.184 . . . . . 1.64

;

run;

data m;

set crv5;

array p[13] r1-r13;

j=1;

tttime= j*0.5;

ttl=10;

do While (tttime < ttl);               /*exit on the last time*/

    if p ne . then                /*find 1st nonmissing value count as */

    do;

        i=j+1;

        ttime=i*0.5;

        if p eq . then

        do until (ttime>=ttl or p ne . ) ; /*keep counting in i, until finds nonmissing value*/

            i=i+1;

            ttime=i*0.5;

        end;

        if (tttime<ttl and ttime<=ttl and (i-j)>1) then

        do;

            ddelta = (p - p) / (i - j); /*ddelta is different for different missing value sections*/

            do k=j+1 to i-1;

                p= p + ddelta*(k-j); /*initial nonmissing value + ddelta*scale to fill out missing values*/

            end;

        end;

    end;

    j=j+1;

    tttime=j*0.5;

end;

run;

Super User
Posts: 10,023

Re: fill out missing value using array linearly

How about:

data crv5;
input r1 r2 r3 r4 r5 r6 r7 r8 r9 r10 r11 r12 r13 ;
cards;
. . 0.4835 . 0.7335 . . . 1.184 . . . . . .
;
run;
data want(drop=i j k miss_count avg);
 set crv5;
 array x{*} r: ;
do i=1 to dim(x)-1;
 miss_count=0;
  if  missing(x{i+1}) and not missing(x{i}) then do;
   do j=i+1 to dim(x);
    if missing(x{j}) then miss_count+1;
     else do;
            avg=(x{j}-x{i})/(miss_count+1);
            leave;
          end;
   end;
  end;
   if miss_count then do;
    do k=i+1 to i+miss_count;
     x{k}=x{i}+ (k-i)*avg; 
    end; avg=.;
   end;
end;
run;

Ksharp

Message was edited by: xia keshan

Respected Advisor
Posts: 3,799

Re: fill out missing value using array linearly

data have;

   input Y @@;

   cards;

0.4835 . 0.7335 . . . 1.184 . . . . . 1.64

proc expand method=join;

   run;

proc print;

   run;

Ask a Question
Discussion stats
  • 2 replies
  • 150 views
  • 6 likes
  • 3 in conversation