Data:
A B C D E
2 3 4 . .
2 3 0 0 .
0 3 4 1 1
0 . 4 0 1
2 0 0 0 1
Ideal output:
A B C D E
2 3 4 1 1
2 3 0 0 1
0 3 4 1 1
0 3 4 0 1
2 0 0 0 1
For each column, there are only 3 possible values: an arbitrary integer, zero, and missing value.
I want to replace the missing values with the non-zero value in the corresponding column.
If the arbitrary integer is zero, then missing value should be replaced by zero.
For actual problem, the number of row and number of columns are not small.
Make two arrays--one with your column names and another with variables to hold the arbitrary integers. Loop through the data set once to get the integers (looping over the columns in the array), then again to output the values, replacing where necessary (again, looping through the columns in the array).
data want(drop=i int1-int5);
do until (eof);
set have end=eof;
array _col a--e;
array _int int1-int5;
do i = 1 to dim(_col);
if _col(i) not in (.,0) then _int(i)=_col(i);
end;
end;
do until (_eof);
set have end=_eof;
do i = 1 to dim(_col);
if missing(_col(i)) then _col(i)=_int(i);
end;
output;
end;
run;
