topic Re: Fill missing value with the average of surrounding values in SAS Data Management

Fill missing value with the average of surrounding values

Xusheng — Fri, 30 Mar 2018 14:38:20 GMT

Hi, I currently have a problem that requires filling the missing value with surrounding value mean. For example:

data have;

input a ;

datalines;

;

run;

Here is what I need:

data want;

input a ;

datalines;

1.5

1.25

1.5

;

run;

As you may notice, the filling method is when there is only one missing, simply take the average of the surrounding values. If there are two continuous missing value, take the average of surrounding values to fill the first missing, then use the same method as only one missing value to fill the second missing.

Any suggestion would be appreciated. Thank you.

Re: Fill missing value with the average of surrounding values

SuryaKiran — Fri, 30 Mar 2018 19:01:37 GMT

PROC EXPAND can be used for interpolating the moving average. But here depending on your condition you need to make some looping to make sure that there is only one missing value between consecutive records to get the output that you desired.

data have;
input a ;
obs=_n_;
datalines;
1
2
2 
.
.
1 
.
2
;
run;
data count_max_mis;
set have ;
if not missing(a) then count=0;
else count+1;
run;

proc sql;
select max(count) into: n_miss
	from count_max_mis;
quit;

%macro interpolate_miss();
%DO i=1 %to &n_miss;

data Inter;
set have;
if not missing(a) then call symput("value","NOTNULL");
else if missing(a) and SYMGET("value")="NOTNULL" then call symput("value","NULL");
else if missing(a) then delete;
run;

PROC EXPAND data=Inter out=Updated_miss(drop=time) method=none;
convert a / transformout=(missonly  cmovave 3 );
run;

data Have;
update have Updated_miss;
by obs;
run;
%end;
%MEND interpolate_miss;
%interpolate_miss;

Re: Fill missing value with the average of surrounding values

PGStats — Fri, 30 Mar 2018 19:46:53 GMT

What is the advantage of such an interpolation method? Its simplicity maybe?

data have;
input a;
datalines;
1
2
2 
.
.
1
.
2
;

data want;
retain _a;
do nb = 1 by 1 until(not missing(a_));
    set have(rename=a=a_);
    end;
do i = 1 to nb;
    set have;
    if missing(a) then a = (_a + a_) / 2;
    _a = a;
    output;
    end;
drop nb i _a a_;
run;

proc print; run;

Re: Fill missing value with the average of surrounding values

Ksharp — Sat, 31 Mar 2018 11:44:41 GMT

data have;
input a ;
datalines;
1
2
2 
.
.
1 
.
2
;
run;

data want;
 set have end=last;
 array x{1000} _temporary_;
 x{_n_}=a;
 if last then do;
  do i=1 to _n_;
    if missing(x{i}) then do;
      prev=x{i-1};next=.;
	  do j=i+1 to _n_;
        if not missing(x{j}) then do;next=x{j};leave;end;
	  end;
	  x{i}=(prev+next)/2;
	end;
  end;

  do k=1 to _n_;
    a=x{k};output;
  end;
 end;
keep a;
run;

Re: Fill missing value with the average of surrounding values

Xusheng — Sat, 31 Mar 2018 14:52:19 GMT

Thank you for the reply.

Re: Fill missing value with the average of surrounding values

Xusheng — Sat, 31 Mar 2018 14:53:28 GMT

Thank you for the reply. It works well.