How to interpolate data from monthly to weekly?

[t75wez1]

Hello all,

I have SAS data set(below) with the variable for bird age in month after 4 weeks old and a percentage named “perc” ,and I want to interpolate “perc” from monthly to weekly (e.g. week1, week 2,… week 44 or 45(=11 months).

I tried to treat the data as time series by using “proc expand” to convert from monthly to weekly, but I need a help on how to code.

Can anyone help me?

Many thanks, 

*****My SAS data set*******************************;
proc format;
value birdage
1='1 Week'
2='2 Weeks'
3='3 Weeks'
4='4 Weeks'
5='2 Months'
6='3 Months'
7='4 Months'
8='5 Months'
9='6 Months'
10='7 Months'
11='8 Months'
12='9 Months'
13='10 Months'
14='11 Months'

;
run;

data WORK.bird;
infile datalines truncover;
input bird_age:32. perc:32.;
format bird_age birdage9.;

datalines;
1 25.34
2 38.05
3 45.23
4 56.73
5 69.28
6 77.87
7 82.4
8 85.88
9 90.05
10 91.43
11 93.12
12 93.69
13 94.27
14 94.4
;;;;
run;

[PaigeMiller]

I tried to treat the data as time series by using “proc expand” to convert from monthly to weekly, but I need a help on how to code.

There's probably no built in method to take this data where 1-4 are weeks and 5-14 are months. Even if you just use 5-14, there's no real relationship between months and weeks, so you would have to create the math for such a method, and then it could be programmed.

Adding: this seems like a poor way to record such data. Keeping the bird_age in days, or even any constant unit (weeks, months) makes the analysis simple. Recording some things in weeks and some things in months make the analysis more difficult.

[Reeza]

---

@PaigeMiller wrote:

Adding: this seems like a poor way to record such data. Keeping the bird_age in days, or even any constant unit (weeks, months) makes the analysis simple. Recording some things in weeks and some things in months make the analysis more difficult.

---

Really common in biology/agriculture fields as growth curves are usually steep for a period of time then become pretty linear after that time. How they measure human babies for example - which was a pain for hospital data when you need to figure out all babies under 28 days old.

[Tom]

I don't know about interpolation.

But if want convert your current coded variable into something that is consistently in weeks a little dimensional analysis shows that you could use:

if birdage in (1:4) then birdweeks=birdage;
else if birdage > 4 then birdweeks=((365.25/52)/12)*(birdage-3);

365.25/52 is the number of weeks per year.  (365.25 days per year over 7 days per week)

12 is the number of months per year.

Weeks/year divided by months/year gives you weeks/month.

Weeks/month times number of months gives you a number of week.

BIRDAGE=5 means 2 months, so subtract 3.

You might want to add 2 weeks so that it is more like the mid-point of the month instead of the beginning of the month.

[ballardw]

Interpolation usually involves two values, such as measured at two time points, and applying the difference between the base (time point) to the measured value. I don't see any second measure to "interpolate" between. I don't see anything to indicate a time, or other measure, related to collection.

Having worked a very extensive program to interpolate at many time intervals between actual measurements that could exceed several months and applying an interpolation of a measured value your data is in no way interpolating anything. As a minimum you need to provide the measure at time zero.

Pick a unit, the smallest practical will yield better results and stay with it. By "4 weeks" do you mean exactly 28 days? Or is this some sort of "between 25 and 32" or other interval? For your Intervals do you want at the beginning of the "month", middle of the "month" or end of the "month". Which brings up as another question "what constitutes a month?"

PS: If you are attempting to get data outside of the collected range (ie after the last time point data is collected) the proper word becomes extrapolate, not interpolate.

[Patrick]

@ballardw  Just asking without really knowing but:

If the OP would convert these week 1 , month 1 etc. to SAS dates (like start with Jan 2022, month one then 01Feb2022 and so on) couldn't this interpolation be done using Proc Expand and whatever method is suitable?

[ballardw]

---

@Patrick wrote:

@ballardw  Just asking without really knowing but:

If the OP would convert these week 1 , month 1 etc. to SAS dates (like start with Jan 2022, month one then 01Feb2022 and so on) couldn't this interpolation be done using Proc Expand and whatever method is suitable?

---

I've never had access to Proc Expand so can't say. I still think it would take at least time periods to do anything though.

[Reeza]

Something like this perhaps:

data temp;
 set bird;
 if bird_age <=4 then age=bird_age*7;
 else  if bird_age >4 then age=intnx('month', 0, bird_age-3, 's');
 format age date9.;
run;

proc expand data=temp out=want to=week ;
convert perc/method=join;
id age;
run;

proc print;run;

[Ksharp]

/*How about this one?*/
data WORK.bird;
infile datalines truncover;
input bird_age:32. perc:32.;
format bird_age birdage9.;
datalines;
1 25.34
2 38.05
3 45.23
4 56.73
5 69.28
6 77.87
7 82.4
8 85.88
9 90.05
10 91.43
11 93.12
12 93.69
13 94.27
14 94.4
;;;;
run;

data temp;
 set bird;
 output;
 do i=1 to 3;
   bird_age=bird_age+0.25;
   call missing(perc);
   output;
 end;
 drop i;
run;

proc expand data=temp out=want ;
convert perc/method=join;
id bird_age;
run;

proc print;run;

[t75wez1 · Accepted solution]

Ksharp, thank you very much. Your elegant solution is exactly what I've been seeking! 

[Reeza]

Since the first 4 are weekly already shouldn't it be:

if bird_age >4 then do i=1 to 3;

Also, this assumes 4 weeks per month which isn't quite correct, it's more like 4.333 weeks per month. 

[t75wez1]

Reeza, thanks very much for pointing out.  it works well after putting  "if bird_age >=4 then do i=1 to 3; "

I don't know how to apply "4.33 weeks per month" into the existing code.   

[Ksharp]

Reeza, It is due to what OP want. I'm assuming 1 2 3 .. is Jan Feb Mar ... And my code is just an example to show to OP .