topic Re: Filling gaps of missing data across arrays in SAS Programming

Filling gaps of missing data across arrays

angeliquec — Sun, 03 Jul 2016 15:45:01 GMT

Hi,

I have a data set with missing data which I'd like to fill in with the next nonmissing value across the array. Or it's like moving values across the array.

data have;

input ID num1 num2 num3 num4;

cards;

1 1 2 . 4

2 1 . . 4

3 1. 3 4

;

run;

data want;

input ID num1 num2 num3 num4;

cards;

1 1 2 4 .

2 1 4 . .

3 1 3 4 .

;

run;

Can I produce the want data without transposing?

Re: Filling gaps of missing data across arrays

rogerjdeangelis — Sun, 03 Jul 2016 16:59:08 GMT

* reverse sort of an aaray;

data have;
input ID num1 num2 num3 num4;
cards;
1 1 2 . 4
2 1 . . 4
3 1 . 3 4
;
run;

Up to 40 obs WORK.HAVE total obs=3

Obs    ID    NUM1    NUM2    NUM3    NUM4

 1      1      1       2       .       4
 2      2      1       .       .       4
 3      3      1       .       3       4


WANT

Up to 40 obs WORK.WANT total obs=3

Obs    ID    NUM1    NUM2    NUM3    NUM4

 1      1      4       2       1       .
 2      2      4       1       .       .
 3      3      4       3       1       .


SOLUTION

data want;
    set have;
    array arr[*] num1-num4;
    array rev[*] num4-num1;
    call sortn(of rev[*]);
  run;

Re: Filling gaps of missing data across arrays

rogerjdeangelis — Sun, 03 Jul 2016 17:09:19 GMT

CORREC

data want;
    set have;
    array arr[*] num1-num4;
    do i=1 to 4;
      if arr[i]=. then arr[i]=constant('BIG');
    end;
    call sortn(of arr[*]);
    do i=1 to 4;
      if arr[i]=constant('BIG') then arr[i]=.;
    end;
    drop i;
  run;


Up to 40 obs WORK.WANT total obs=3

Obs    ID    NUM1    NUM2    NUM3    NUM4

 1      1      1       2       4       .
 2      2      1       4       .       .
 3      3      1       3       4       .

TION

Re: Filling gaps of missing data across arrays

PGStats — Sun, 03 Jul 2016 17:45:32 GMT

No tricks; just simple programming:

data want;
set have;
array num num:;
j = 0;
do i = 1 to dim(num);
    if not missing(num{i}) then do;
        j + 1;
        num{j} = num{i};
        end;
    end;
do i = j + 1 to dim(num);
    call missing(num{i});
    end;
drop i j;
run;

Re: Filling gaps of missing data across arrays

KachiM — Sun, 03 Jul 2016 20:03:16 GMT

Another way:

Look for a missing value, note the position(i). Then move forward to find the next non-missing value to replace the i-th value while marking the j-th value as missing. Skip the check. Repeat the above step for the next i-th value.

data need;
   set have;
      array num num: ;
      do i = 1 to dim(num) - 1;
         if missing(num[i]) then 
         do j = i + 1  to dim(num);
            if not missing(num[j]) then do; 
               num[i] = num[j]; num[j] = .; leave; 
            end;
         end;
      end;
drop i j;
run;

Re: Filling gaps of missing data across arrays

Ksharp — Mon, 04 Jul 2016 04:57:24 GMT

data have;
input ID num1 num2 num3 num4;
cards;
1 1 2 . 4
2 1 . . 4
3 1 . 3 4
;
run;
data want;
 set have;
 array x{4} _num1-_num4;
 array y{4} num1-num4;

 n=0;
 do i=1 to dim(y);
  if not missing(y{i}) then do;
   n+1;x{n}=y{i};
  end;
 end;
drop i n num:;
run;