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?
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;
* 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;
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
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.