BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Xusheng
Obsidian | Level 7

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

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

Here is what I need:

data want;
input a ;
datalines;
1
2
1.5
1.25
1.5
2
;
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.
1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

View solution in original post

5 REPLIES 5
SuryaKiran
Meteorite | Level 14

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;
Thanks,
Suryakiran
Xusheng
Obsidian | Level 7
Thank you for the reply.
PGStats
Opal | Level 21

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;
PG
Xusheng
Obsidian | Level 7
Thank you for the reply. It works well.
Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1459 views
  • 3 likes
  • 4 in conversation