Fill missing values with average of previous/next values

[pamplemouse22]

thank you! this seems to fill in averages with nearest before and after, but how can I adjust this to only fill in values if the values it pulls from are within 7 days? thanks!

[Ksharp · Accepted solution]

OK. That would be more simple.

data have; 
input date : anydtdte. zip temp; 
format date date9.;
datalines; 
2000jan1  90001 50
2000jan2  90001 51
2000jan3  90001 53
2000jan4  90001 . 
2000jan5  90001 49
2000jan6  90001 . 
2000jan7  90001 . 
2000jan8  90001 . 
2000jan9  90001 50
2000jan10 90001 55
;
run; 


data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h(dataset:'have',hashexp:20);
  h.definekey('zip','date');
  h.definedata('temp');
  h.definedone();
 end;
set have;
if missing(temp) then do;
 do i=1 to 7;
 _date=date-i;
 call missing(temp);
 if h.find(key:zip,key:_date)=0 and not missing(temp) then do;lag=temp;leave;end;
 end;

 do i=1 to 7;
 _date=date+i;
 call missing(temp);
 if h.find(key:zip,key:_date)=0 and not missing(temp) then do;next=temp;leave;end;
 end;

 temp=(lag+next)/2;
end;
format _date date9.;
drop  lag next i _date;
run;

[pamplemouse22]

This works! Thank you so much. I have not used hash before - seems like a very efficient solution.

Can you please explain what the following line does?
Thanks!

call missing(temp);
if h.find(key:zip,key:_date)=0 and not missing(temp) then do;lag=temp;leave;end;

[Ksharp]

'call missing(temp);'

Due to TEMP is in Hash Table ,so I set it  missing before invoking Hash Table, to avoid to retain the lag value of TEMP.

'if h.find(key:zip,key:_date)=0 and not missing(temp) then do;lag=temp;leave;end;'

Invoke Hash Table ,according to key variables zip and _date to find TEMP value,

if TEMP is not missing then TEMP is what I want ,and set its value to lag , and leave the whole loop,because of finding what I want and no need to loop the next ones.

[pamplemouse22]

great! this is perfect. thank you so much. what would you suggest is the best way to run this same loop for other variables? im creating a macro variable for this - do you agree? 

[Ksharp]

You can just replace variable TEMP with the variable you need .

or rename your name as temp.

like:

data have;

  set your-table(rename=(your-variable=temp));

run;

If you are trying to make macro, make sure use double quote around macro variable.

like in Hash Table:

h.definedata('temp');

should be 

h.definedata(" &macro_variable ");

[novinosrin]

data have;

input date $ zip temp;

datalines;

jan1  90001 50

jan2  90001 51

jan3  90001 53

jan4  90001 .

jan5  90001 49

jan6  90001 .

jan7  90001 .

jan8  90001 .

jan9  90001 50

jan10 90001 55

;

run;

data want;

drop _: rc;

if _N_ = 1 then do;

   declare hash h();

   rc = h.defineKey('zip','__n');

   rc = h.defineData('temp');

   rc = h.defineDone();

end;

do _n=1 by 1 until(last.zip);

     set have;

     by zip;

     if _n>1 and  not missing(temp) and lag(temp)=. then

           do;

                _avg=mean(temp,_temp);

                __temp=temp;

                temp=_avg;

                rc=h.check();

                if rc=0 then h.replace();

                do __n=__n by -1 while(rc=0);

                      rc=h.check();

                      if rc=0 then h.replace();

                end;

                temp=__temp;

           end;

     if not missing(temp) then _temp=temp;

     else if missing(temp) then

           do;

                __n=_n;

                h.add();

           end;

end;

do __n=1 by 1 until(last.zip);

     set have;

     by zip;

    rc=h.find();

     output;

end;

run;

/*Part B*/

@pamplemouse22 I'm afraid I require more and clearer explanation on the 7 day logic fitment for me to tweak the code at-least for my learning as by no means my solutions are worth moving to production as I believe quality of mine is poor 😞

Anyway here is my question, I am dividing your HAVE by 7 to see the weeks as groups

data have1;

input date :date9. zip temp;

if lag(zip) ne zip then k=0;

k=ceil(date/7); /*notice here*/

format date date9.;

datalines;

--Your datalines follow--

;

Okay, The week starting 8Jan to 14jan for 9002 has missing imputations for the dates 13 and 14 in your example. Isn't that part of the valid 7 days and requires to be imputed? and likewise for others My English seems awful. Pardon me for my poor comprehension.