Filling in missing sequence number and linearly interpolating between values

linaya — Tue, 04 Sep 2018 13:53:48 GMT

Hello!

I have a data set that contrains daily records of milk yields for each individual animal. However, some days are missing in the data (goes from D2-D4 with no D3). I want to fill in these missing days for each individual animal. However, every animal has a different lactation length (ie some go from D1-D400, others from D5-D363). I do have the maximum lactation length as a value in my data set.

Also, once I have filled in all the missing sequence numbers, how do I linearly interpolate the corresponding milk yields for the missing values?

My data looks something like this:

data raw;

input cow_ID day milk_yield max_length

datalines;

1 1 21.6 335

1 2 21.6 335

1 4 21.6 335

...

1 335 16.8 335

3 1 18.4 410

3 3 20.1 410

...

3 410 10.8 410;

run;

So for example, for cow 1 I want to insert a row for her missing day 3 and the linearly interpolate the milk yield for day 3 based on the data from day 2 and day 4.

Thank you in advance

Re: Filling in missing sequence number and linearly interpolating between values

Haikuo — Tue, 04 Sep 2018 16:34:16 GMT

interpolating from 2 numbers is not too difficult to manage, notice that the one- missing end scenario is also included.

data raw;
input cow_ID day milk_yield max_length ;
datalines;
1 1 21.6 335
1 2 21.6 335
1 4 21.6 335
1 335 16.8 335
3 3 18.4 410
3 5 20.1 410
3 410 10.8 415
;

run;


data want;
   set raw;
   by cow_id;
   set raw(firstobs=2 keep=day milk_yield rename=(day=_day milk_yield=_mk)) raw(obs=1 drop=_all_);
if first.cow_id and day>1 then do;
   _fday=day;
   do day=1 by 1 while (day<_fday);
      output;
	end;
	end;
   if not last.cow_id then do;
do _n_=1 by 1 to _day-day;
    if _n_=1 then do;
    _mk1=milk_yield;
	_day1=day;
	end;
milk_yield=_mk1+(day-_day1)*((_mk-_mk1)/(_day-_day1));
	
  output;
  day=day+1;
end;
end;
else do;
  do while (day<=max_length);

output;
day=day+1;
end;
end;
drop _:;;
run;

topic Re: Filling in missing sequence number and linearly interpolating between values in SAS Programming

Filling in missing sequence number and linearly interpolating between values

Re: Filling in missing sequence number and linearly interpolating between values