@alfonsomorenom wrote:
I need to fill the nulls of a column with the mean sum of the division of two columns An example would be
A B C ... B_01 C_01
5 .
5 2
7 3
9 3
4 .
Well, I would like the missing value for column B to be (2/5 + 3/7 + 3/9) / 3 * its corresponding column A, which for the first row would be 2.07
My calculations show that the result should be 0.387, not 2.07. Are you sure you have explained this properly?
I find that SQL is a very poor choice for arithmetic when lots of columns are involved, and that DATA steps with arrays or PROCs would be far superior than SQL in that case.
Since you have provided only one column, rather than a few more (we don't need all 60), I will provide my code for this one column.
data intermediate(keep=mean);
set have end=eof;
if not missing(b) then do;
sum+b/a;
count+1;
end;
if eof then do;
mean=sum/count;
output;
end;
run;
data want;
if _n_=1 then set intermediate;
set have;
if missing(b) then b=mean;
drop mean;
run;
To extend this to 60 columns, I can't test this, but something like this ought to work
data intermediate(keep=mean1-mean60);
set have end=eof;
array v b1-b60;
array s sum1-sum60;
array c count1-count60;
array m mean1-mean60;
do i=1 to dim(v);
if not missing(v(i)) then s(i)+v(i)/a;
c(i)+1;
end;
if eof then do;
m(i)=s(i)/c(i);
output;
end;
run;
data want;
if _n_=1 then set intermediate;
set have;
array v b1-b60;
array m mean1-mean60;
do i=1 to dim(v);
if missing(v(i)) then v(i)=m(i);
end;
drop mean1-mean60 i;
run;
... View more
