topic How to interpolate weather data in SAS Programming

How to interpolate weather data

Singham20 — Mon, 14 May 2018 18:49:59 GMT

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.

Re: How to interpolate weather data

Reeza — Mon, 14 May 2018 18:54:44 GMT

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.

@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.

Re: How to interpolate weather data

Reeza — Mon, 14 May 2018 18:56:39 GMT

You'll need to transpose your data as well before using PROC TIMESERIES, you can use PROC TRANSPOSE for that.

Re: How to interpolate weather data

ballardw — Mon, 14 May 2018 19:58:12 GMT

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 ).

Re: How to interpolate weather data

Reeza — Mon, 14 May 2018 20:22:30 GMT

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.

Re: How to interpolate weather data

ballardw — Mon, 14 May 2018 22:06:24 GMT

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.

Re: How to interpolate weather data

Singham20 — Tue, 15 May 2018 18:05:00 GMT

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.

Re: How to interpolate weather data

ballardw — Wed, 16 May 2018 15:44:31 GMT

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;