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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.