fill the nulls of a column with the mean sum of the division of two columns

alfonsomorenom — Sat, 28 Jan 2023 23:52:18 GMT

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

I have thought about doing this, but it turns out that I have 60 columns with which to do it and the only way it comes to mi mind is to do this 60 times.

Proc sql créate table new as

Select *, sum(B/A)/sum(case when B is missimg then . else 1) end as new_B, sum(B/A)/sum(case when B is missimg then . else 1) end as new_B_01 from table_one;

Quit;

Thanks

Re: fill the nulls of a column with the mean sum of the division of two columns

PaigeMiller — Sun, 29 Jan 2023 00:56:01 GMT

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

topic Re: fill the nulls of a column with the mean sum of the division of two columns in SAS Programming

fill the nulls of a column with the mean sum of the division of two columns

Re: fill the nulls of a column with the mean sum of the division of two columns