Solved
Contributor
Posts: 55

# Filling gaps of missing data across arrays

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?

Accepted Solutions
Solution
‎07-09-2016 07:56 AM
Super User
Posts: 10,788

## Re: Filling gaps of missing data across arrays

``````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;``````

All Replies
Valued Guide
Posts: 505

## Re: Filling gaps of missing data across arrays

``````* 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;

``````
Valued Guide
Posts: 505

## Re: Filling gaps of missing data across arrays

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

Posts: 5,543

## Re: Filling gaps of missing data across arrays

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;``````
PG
Super Contributor
Posts: 326

## Re: Filling gaps of missing data across arrays

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;``````
Solution
‎07-09-2016 07:56 AM
Super User
Posts: 10,788

## Re: Filling gaps of missing data across arrays

``````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;``````
☑ This topic is solved.