## How to interpolate weather data

Solved
Occasional Contributor
Posts: 6

# How to interpolate weather data

Hi all,

I have an excel sheet with weather data for the full year (2017) in 3 hour increments as shown below:

 TEMP-MAX TEMP-MIN TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21 Date 32.00 23.00 27.00 25.00 23.00 26.10 32.00 30.00 28.00 28.00 1/1/2017 32.00 28.00 28.00 28.00 28.90 30.00 30.90 32.00 32.00 32.00 1/2/2017 32.00 3.00 32.00 32.00 25.00 14.00 12.00 10.90 6.10 3.00 1/3/2017 3.00 -5.10 1.00 -2.00 -5.10 -4.00 1.90 3.00 1.00 0.00 1/4/2017 1.00 -6.00 0.00 -2.90 -6.00 -2.90 1.00 1.00 -2.00 -6.00 1/5/2017 5.00 -9.00 -9.00 -9.00 -9.00 -6.00 1.90 5.00 3.90 1.90 1/6/2017 8.10 0.00 0.00 0.00 1.00 3.00 8.10 8.10 3.90 1.00 1/7/2017 16.00 -4.00 -0.90 -2.90 -4.00 1.00 10.90 12.90 14.00 16.00 1/8/2017 26.10 18.00 18.00 19.00 18.00 19.00 21.00 21.90 24.10 26.10 1/9/2017 27.00 5.00 27.00 26.10 27.00 27.00 21.90 12.90 8.10 5.00 1/10/2017 9.00 5.00 5.00 6.10 6.10 6.10 9.00 8.10 6.10 6.10 1/11/2017

I need to create a SAS dataset that will interpolate this 3 hour weather data into hourly data as shown below:

*temp is made up to give example

 hourend Temp 01JAN2017:01:00:00 24.55 01JAN2017:02:00:00 25.3 01JAN2017:03:00:00 26.05 01JAN2017:04:00:00 26.8 01JAN2017:05:00:00 27.4833 01JAN2017:06:00:00 28.1667 01JAN2017:07:00:00 28.85 01JAN2017:08:00:00 29.85 01JAN2017:09:00:00 30.85 01JAN2017:10:00:00 31.85 01JAN2017:11:00:00 33.65 01JAN2017:12:00:00 35.45 01JAN2017:13:00:00 37.25 01JAN2017:14:00:00 37.5 01JAN2017:15:00:00 37.75 01JAN2017:16:00:00 38 01JAN2017:17:00:00 37 01JAN2017:18:00:00 36 01JAN2017:19:00:00 35 01JAN2017:20:00:00 32.8666 01JAN2017:21:00:00 30.7333 01JAN2017:22:00:00 28.6 01JAN2017:23:00:00 27.25 02JAN2017:00:00:00 25.9 02JAN2017:01:00:00 23.55 02JAN2017:02:00:00 22.9333 02JAN2017:03:00:00 22.3167 02JAN2017:04:00:00 21.7 02JAN2017:05:00:00 21.5167 02JAN2017:06:00:00 21.3333 02JAN2017:07:00:00 21.15 02JAN2017:08:00:00 22.3833 02JAN2017:09:00:00 23.6167 02JAN2017:10:00:00 24.85 02JAN2017:11:00:00 27.1 02JAN2017:12:00:00 29.35

Thank you very much for all you help.

Accepted Solutions
Solution
‎05-15-2018 02:02 PM
Super User
Posts: 13,941

## Re: How to interpolate weather data

Here is one way.  I combined the creation of a datetime variable and the transposition in the read step.

There are things to watch with the start stop of a days records so saving and restoring the values for the output may look odd.

Also I will leave it to you what you want to do about the interval between the last recorded temp and midnight. (Did you think that was an issue?).

Also this assumes that you have no missing records. If you do then there are things you need to address regarding the divisor to determine the interval step (hint: hours between current datetime and last date time with temp (less one) which requires modifying the transpose array code) and then number of steps in the do loop in the want datastep code.

If you do not have access to SAS/ETS you might need something like this.

```data have;
informat TEMP_MAX TEMP_MIN TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21 best8. Date anydtdte.;
format date mmddyy10.;
input TEMP_MAX TEMP_MIN TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21 Date  ;
array hr TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21;
do i=1 to dim(hr);
hour  = (i-1)*3;
hrtemp= hr[i];
datetime= dhms(date,hour,0,0);
output;
end;
format datetime datetime.;
drop i TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21;
label hrtemp='Hourly Temp';
datalines;
32.00 23.00 27.00 25.00 23.00 26.10 32.00 30.00 28.00 28.00 1/1/2017
32.00 28.00 28.00 28.00 28.90 30.00 30.90 32.00 32.00 32.00 1/2/2017
32.00 3.00 32.00 32.00 25.00 14.00 12.00 10.90 6.10 3.00 1/3/2017
3.00 -5.10 1.00 -2.00 -5.10 -4.00 1.90 3.00 1.00 0.00 1/4/2017
;
run;

data want;
set have;
by date;
ldt = lag(datetime);
ltemp = lag(hrtemp);
savedt = datetime;
savetemp= hrtemp;
if first.date then output;
else do ;
int = (hrtemp - ltemp) / 3;
do i= 1 to 2;
datetime=intnx('hour',ldt,i);
hrtemp= round(ltemp + i*int,0.01);
output;
end;
datetime=savedt;
hrtemp=savetemp;
output;
end;
keep TEMP_MAX TEMP_MIN date datetime hrtemp;
run;
```

And you might want to thank SAS for providing the datetime data type and the functions associated with it. I had to write code in FORTRAN to do interpolation that crossed the 2000 century boundary and take into consideration of leap days and such.

All Replies
Super User
Posts: 24,010

## Re: How to interpolate weather data

[ Edited ]

Look at PROC TIMESERIES and PROC EXPAND - this assumes you have a SAS/ETS license. If you do not, then you have to do it manually.

Spoiler

@Singham20 wrote:

Hi all,

I have an excel sheet with weather data for the full year (2017) in 3 hour increments as shown below:

 TEMP-MAX TEMP-MIN TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21 Date 32.00 23.00 27.00 25.00 23.00 26.10 32.00 30.00 28.00 28.00 1/1/2017 32.00 28.00 28.00 28.00 28.90 30.00 30.90 32.00 32.00 32.00 1/2/2017 32.00 3.00 32.00 32.00 25.00 14.00 12.00 10.90 6.10 3.00 1/3/2017 3.00 -5.10 1.00 -2.00 -5.10 -4.00 1.90 3.00 1.00 0.00 1/4/2017 1.00 -6.00 0.00 -2.90 -6.00 -2.90 1.00 1.00 -2.00 -6.00 1/5/2017 5.00 -9.00 -9.00 -9.00 -9.00 -6.00 1.90 5.00 3.90 1.90 1/6/2017 8.10 0.00 0.00 0.00 1.00 3.00 8.10 8.10 3.90 1.00 1/7/2017 16.00 -4.00 -0.90 -2.90 -4.00 1.00 10.90 12.90 14.00 16.00 1/8/2017 26.10 18.00 18.00 19.00 18.00 19.00 21.00 21.90 24.10 26.10 1/9/2017 27.00 5.00 27.00 26.10 27.00 27.00 21.90 12.90 8.10 5.00 1/10/2017 9.00 5.00 5.00 6.10 6.10 6.10 9.00 8.10 6.10 6.10 1/11/2017

I need to create a SAS dataset that will interpolate this 3 hour weather data into hourly data as shown below:

*temp is made up to give example

 hourend Temp 01JAN2017:01:00:00 24.55 01JAN2017:02:00:00 25.3 01JAN2017:03:00:00 26.05 01JAN2017:04:00:00 26.8 01JAN2017:05:00:00 27.4833 01JAN2017:06:00:00 28.1667 01JAN2017:07:00:00 28.85 01JAN2017:08:00:00 29.85 01JAN2017:09:00:00 30.85 01JAN2017:10:00:00 31.85 01JAN2017:11:00:00 33.65 01JAN2017:12:00:00 35.45 01JAN2017:13:00:00 37.25 01JAN2017:14:00:00 37.5 01JAN2017:15:00:00 37.75 01JAN2017:16:00:00 38 01JAN2017:17:00:00 37 01JAN2017:18:00:00 36 01JAN2017:19:00:00 35 01JAN2017:20:00:00 32.8666 01JAN2017:21:00:00 30.7333 01JAN2017:22:00:00 28.6 01JAN2017:23:00:00 27.25 02JAN2017:00:00:00 25.9 02JAN2017:01:00:00 23.55 02JAN2017:02:00:00 22.9333 02JAN2017:03:00:00 22.3167 02JAN2017:04:00:00 21.7 02JAN2017:05:00:00 21.5167 02JAN2017:06:00:00 21.3333 02JAN2017:07:00:00 21.15 02JAN2017:08:00:00 22.3833 02JAN2017:09:00:00 23.6167 02JAN2017:10:00:00 24.85 02JAN2017:11:00:00 27.1 02JAN2017:12:00:00 29.35

Thank you very much for all you help.

Super User
Posts: 24,010

## Re: How to interpolate weather data

You'll need to transpose your data as well before using PROC TIMESERIES, you can use PROC TRANSPOSE for that.
Super User
Posts: 13,941

## Re: How to interpolate weather data

What precision are you going to limit the resulting interpolations to? A very large percentage of your example values show 0 to two decimal places making think that your recording "system" doesn't actually record decimal values most of the time (and sort of wonder about the few that do and why the only decimals are .10 and .90 ).

Super User
Posts: 24,010

## Re: How to interpolate weather data

And another component, if you’re interested you should also use nearby stations in your interpolation and interpolation for temperature and precipitation are vastly different.
Solution
‎05-15-2018 02:02 PM
Super User
Posts: 13,941

## Re: How to interpolate weather data

Here is one way.  I combined the creation of a datetime variable and the transposition in the read step.

There are things to watch with the start stop of a days records so saving and restoring the values for the output may look odd.

Also I will leave it to you what you want to do about the interval between the last recorded temp and midnight. (Did you think that was an issue?).

Also this assumes that you have no missing records. If you do then there are things you need to address regarding the divisor to determine the interval step (hint: hours between current datetime and last date time with temp (less one) which requires modifying the transpose array code) and then number of steps in the do loop in the want datastep code.

If you do not have access to SAS/ETS you might need something like this.

```data have;
informat TEMP_MAX TEMP_MIN TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21 best8. Date anydtdte.;
format date mmddyy10.;
input TEMP_MAX TEMP_MIN TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21 Date  ;
array hr TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21;
do i=1 to dim(hr);
hour  = (i-1)*3;
hrtemp= hr[i];
datetime= dhms(date,hour,0,0);
output;
end;
format datetime datetime.;
drop i TEMP0 TEMP3 TEMP6 TEMP9 TEMP12 TEMP15 TEMP18 TEMP21;
label hrtemp='Hourly Temp';
datalines;
32.00 23.00 27.00 25.00 23.00 26.10 32.00 30.00 28.00 28.00 1/1/2017
32.00 28.00 28.00 28.00 28.90 30.00 30.90 32.00 32.00 32.00 1/2/2017
32.00 3.00 32.00 32.00 25.00 14.00 12.00 10.90 6.10 3.00 1/3/2017
3.00 -5.10 1.00 -2.00 -5.10 -4.00 1.90 3.00 1.00 0.00 1/4/2017
;
run;

data want;
set have;
by date;
ldt = lag(datetime);
ltemp = lag(hrtemp);
savedt = datetime;
savetemp= hrtemp;
if first.date then output;
else do ;
int = (hrtemp - ltemp) / 3;
do i= 1 to 2;
datetime=intnx('hour',ldt,i);
hrtemp= round(ltemp + i*int,0.01);
output;
end;
datetime=savedt;
hrtemp=savetemp;
output;
end;
keep TEMP_MAX TEMP_MIN date datetime hrtemp;
run;
```

And you might want to thank SAS for providing the datetime data type and the functions associated with it. I had to write code in FORTRAN to do interpolation that crossed the 2000 century boundary and take into consideration of leap days and such.

Occasional Contributor
Posts: 6

## Re: How to interpolate weather data

Thank you so much!  This worked for Hours 0 to 21.  I will add a step to take into account for the last 3 hours.

Super User
Posts: 13,941

## Re: How to interpolate weather data

```data want;
set have end=last;
by date;
ldt = lag(datetime);
ltemp = lag(hrtemp);
savedt = datetime;
savetemp= hrtemp;
if _n_=1 then output;
else do ;
int = (hrtemp - ltemp) / 3;
do i= 1 to 2;
datetime=intnx('hour',ldt,i);
hrtemp= round(ltemp + i*int,0.01);
output;
end;
datetime=savedt;
hrtemp=savetemp;
output;
end;
/* assumes ends on hour 21 and increment the last value of
of temp using the inteval between 18 and 21 to create
values for hours 22 and 23*/
if last then do;
do i= 1 to 2; /* 3 if you want a midnight*/
datetime=intnx('hour',datetime,1);
hrtemp= round(hrtemp + int,0.01);
output;
end;
end;
keep TEMP_MAX TEMP_MIN date datetime hrtemp;
run;
```
☑ This topic is solved.