DATA Step, Macro, Functions and more

How to interpolate weather data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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-MAXTEMP-MINTEMP0TEMP3TEMP6TEMP9TEMP12TEMP15TEMP18TEMP21Date
32.0023.0027.0025.0023.0026.1032.0030.0028.0028.001/1/2017
32.0028.0028.0028.0028.9030.0030.9032.0032.0032.001/2/2017
32.003.0032.0032.0025.0014.0012.0010.906.103.001/3/2017
3.00-5.101.00-2.00-5.10-4.001.903.001.000.001/4/2017
1.00-6.000.00-2.90-6.00-2.901.001.00-2.00-6.001/5/2017
5.00-9.00-9.00-9.00-9.00-6.001.905.003.901.901/6/2017
8.100.000.000.001.003.008.108.103.901.001/7/2017
16.00-4.00-0.90-2.90-4.001.0010.9012.9014.0016.001/8/2017
26.1018.0018.0019.0018.0019.0021.0021.9024.1026.101/9/2017
27.005.0027.0026.1027.0027.0021.9012.908.105.001/10/2017
9.005.005.006.106.106.109.008.106.106.101/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

hourendTemp
01JAN2017:01:00:0024.55
01JAN2017:02:00:0025.3
01JAN2017:03:00:0026.05
01JAN2017:04:00:0026.8
01JAN2017:05:00:0027.4833
01JAN2017:06:00:0028.1667
01JAN2017:07:00:0028.85
01JAN2017:08:00:0029.85
01JAN2017:09:00:0030.85
01JAN2017:10:00:0031.85
01JAN2017:11:00:0033.65
01JAN2017:12:00:0035.45
01JAN2017:13:00:0037.25
01JAN2017:14:00:0037.5
01JAN2017:15:00:0037.75
01JAN2017:16:00:0038
01JAN2017:17:00:0037
01JAN2017:18:00:0036
01JAN2017:19:00:0035
01JAN2017:20:00:0032.8666
01JAN2017:21:00:0030.7333
01JAN2017:22:00:0028.6
01JAN2017:23:00:0027.25
02JAN2017:00:00:0025.9
02JAN2017:01:00:0023.55
02JAN2017:02:00:0022.9333
02JAN2017:03:00:0022.3167
02JAN2017:04:00:0021.7
02JAN2017:05:00:0021.5167
02JAN2017:06:00:0021.3333
02JAN2017:07:00:0021.15
02JAN2017:08:00:0022.3833
02JAN2017:09:00:0023.6167
02JAN2017:10:00:0024.85
02JAN2017:11:00:0027.1
02JAN2017:12:00:0029.35

 

Thank you very much for all you help.


Accepted Solutions
Solution
Tuesday
Super User
Posts: 12,994

Re: How to interpolate weather data

Posted in reply to Singham20

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.

 

View solution in original post


All Replies
Super User
Posts: 22,818

Re: How to interpolate weather data

[ Edited ]
Posted in reply to Singham20

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: 22,818

Re: How to interpolate weather data

Posted in reply to Singham20
You'll need to transpose your data as well before using PROC TIMESERIES, you can use PROC TRANSPOSE for that.
Super User
Posts: 12,994

Re: How to interpolate weather data

Posted in reply to Singham20

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: 22,818

Re: How to interpolate weather data

Posted in reply to Singham20
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
Tuesday
Super User
Posts: 12,994

Re: How to interpolate weather data

Posted in reply to Singham20

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: 12,994

Re: How to interpolate weather data

Posted in reply to Singham20
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 108 views
  • 0 likes
  • 3 in conversation