BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Singham20
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

7 REPLIES 7
Reeza
Super User

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.


 

 

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

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

Reeza
Super User
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.
ballardw
Super User

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.

 

Singham20
Obsidian | Level 7

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. 

ballardw
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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