Time-series: Covert to Even interval or frequency with missing data

[jamescase]

Hi,

I am new to SAS and have read through previous questions/replys, but I'm still searching for help for transforming my time-series data. I currently have gaps in my time-series data for temperature (i.e. I do not have a complete time-series data set). I would like to insert the missing datetime values (x variable) at a set interval of 1 minute. For each new datetime I would like to add a zero or na for the measured variable (y variable), which in my case, again, is temperature. I am working with an excel file (.xls) and my datetime is currently formated to dd/mm/yyyy hh:mm:ss. Again, I would like to set the output file to have a frequency of 1 minute. I realize that having no data could be considered to be an incomplete time-sereries data set, but in my case no data is data. Can anyone provide any advice? I have already tried to use this source http://support.sas.com/rnd/app/examples/ets/missval/index.htm,  which shows that new rows for month can be added at a freqency of 1 month. Fingers crossed that I didn't break any ettiques for SAS questions on my first post!

Kind regards,

james

[Linlin]

Is this helpful?

data have(keep=x y);

  input dt_n_tm $20. y;

  TheDate = input(scan(dt_n_tm,1,' '),mmddyy10.);

  TheTime = input(scan(dt_n_tm,2,' '),time8.);

  x = input(put(TheDate,date8.)||' '||scan(dt_n_tm,2,''),datetime.);

  cards;

01/03/2011 10:32:00 50

01/04/2011 10:35:00 55

01/05/2011 11:45:00 60

;

run;

proc expand data=have out=temp to=minute method=none;

      id x;

   run;

data want;

   set temp;

   if y=. then y=0;

run;

proc print;run;

[jamescase]

Thanks so much for your response, Linlin. When I copy and paste your code into SAS (9.0, Windows Version) and press the running man, I receive the following message:

NOTE: No observations in data set WORK.WANT.

NOTE: PROCEDURE PRINT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

Perhaps I am doing something wrong--very likely since I am a newbie to SAS.

Also, I will need to import an excel file "C:\Test.xls", which has two columns [one named time and the other named temp]

time	temp
8/20/2009 18:35	24.6
8/20/2009 18:36	24.8
8/20/2009 18:37	24.8
8/20/2009 18:38	24.9
8/20/2009 18:39	24.9
8/20/2009 18:40	24.9
8/20/2009 18:46	25.4
8/20/2009 18:47	25.4
8/20/2009 18:48	25.4
8/20/2009 18:49	25.4
8/20/2009 18:50	25.6
8/20/2009 18:51	25.6
8/20/2009 18:52	25.7
8/20/2009 18:53	25.7
8/20/2009 18:54	25.8

I made rows bold where I am missing data and would like to insert new values at 1-minute intervals. And yes, your code for inserting new temp values as 0 when y=. looks to be on the right track for what I am hoping to accomplish.

Also, when I import my excel file using the File>Import Data option, my data set appears to be imported without the the time (i.e. only the date value is imported, but the column was formated as dd/mm/yy hh:mm in excel prior to importing).

Thank you again for your feedback, but I still need more HELP on this one!

Cheers,

james

[art297]

Attach a copy of your spreadsheet and show the part of your log that appeared when you ran proc expand.  Your site license may not include ETS which one would need in order to run proc expand.

[jamescase]

Hi,

I opened a new editor and pasted in your code. It worked perfectly this round! Perhaps I made an error and told SAS to refer to a different data set before. Taking notes and learning more and more about the software with each error and success.

I've attached my data sample to my original post. I've been trying to adapt your code to the data in my excel file...no success yet though.

Thank you so much again for your time.

james

[jamescase]

Hi Linlin,

Can you recommend how to adapt your code to an exel file called C:\Test.xls? I've save the excel file as a 97 workbook. Column 1 is time and column 2 is temeprature. The format for my time column is dd/mm/yyyy hh:mm. I've been working at this for a while now, but can seem to get things right. thanks in advance for your response!

james

[art297]

James,

Your time variable wasn't coming in as a datetime variable, per se, but rather as a date and the fraction of the day that the time represented.

I think I've corrected that in the following adaptation of Linlin's originally suggested code.  In case you are wondering, I used the dhms function to build a datetime variable, but had to take the integer (int) of it as the function apparently returns some fuzz at the end which, if not truncated, would confuse proc expand:

PROC IMPORT OUT= work.data_in

  DATAFILE= "C:test.xls"

  DBMS=excel REPLACE;

  GETNAMES=YES;

run;

data have (drop=in_:);

  set data_in (rename=(time=in_date));

  in_time=(in_date-int(in_date))*(24*60*60);

  time=int(dhms(int(in_date),hour(in_time),

        minute(in_time),second(in_time)));

run;

proc expand data=have

            out=want

            to=minute method=none;

  id time;

run;

data want;

  set want;

  format time datetime22.;

  if missing(temp) then temp=0;

run;

[Linlin]

Hi Art,

Would you please help me to fiqure out why my code only produces 19 observations?  there should be 20.

I read in James' excel correctly

                            1    20AUG09:18:35:00    24.6

                            2    20AUG09:18:36:00    24.8

                            3    20AUG09:18:37:00    24.8

                            4    20AUG09:18:38:00    24.9

                            5    20AUG09:18:39:00    24.9

                            6    20AUG09:18:40:00    24.9

                            7    20AUG09:18:46:00    25.4

                            8    20AUG09:18:47:00    25.4

                            9    20AUG09:18:48:00    25.4

                           10    20AUG09:18:49:00    25.4

                           11    20AUG09:18:50:00    25.6

                           12    20AUG09:18:51:00    25.6

                           13    20AUG09:18:52:00    25.7

                           14    20AUG09:18:53:00    25.7

                           15    20AUG09:18:54:00    25.8

after I ran proc expand, the variable "temp" of second observation (time=20AUG09:18:36) is missing,

and the last observation (time= 20AUG09:18:54:00) is not in the final dataset.

                                Obs             time    temp

                             1    20AUG09:18:35    24.6

                             2    20AUG09:18:36     0.0

                             3    20AUG09:18:37    24.8

                             4    20AUG09:18:38    24.8

                             5    20AUG09:18:39    24.9

                             6    20AUG09:18:40    24.9

                             7    20AUG09:18:41    24.9

                             8    20AUG09:18:42     0.0

                             9    20AUG09:18:43     0.0

                            10    20AUG09:18:44     0.0

                            11    20AUG09:18:45     0.0

                            12    20AUG09:18:46     0.0

                            13    20AUG09:18:47    25.4

                            14    20AUG09:18:48    25.4

                            15    20AUG09:18:49    25.4

                            16    20AUG09:18:50    25.4

                            17    20AUG09:18:51    25.6

                            18    20AUG09:18:52    25.6

                            19    20AUG09:18:53    25.7

Thanks!  Linlin

libname test 'c:\temp\test.xls'

     mixed=yes

     stringDates=yes

     scanTime=yes;

run;

data have;

    set test.'sheet1$'n(dbSasType=( time=dateTime ));

format time datetime.;

run;

proc print data=have;

  title from dataset have;

run;

proc contents data=have; run;

proc expand data=have out=new to=minute method=none;

      id time;

       /* why the last observation is not in the final dataset want? */

       /* why the temp of second observation is missing?  */

run;

data want;

   set new;

   if temp=. then temp=0;

run;

proc print data=&syslast;

  title from dataset want;

run;

libname test clear;

title;

[art297]

Linlin,

First, thank you for teaching me something new!  I didn't know about the stringDates option.

If you look at your result, you will notice that some of the temp values that actually existed, ended up being canceled out.

The problem, like the problem I had to overcome using proc import, is that the datetime values came in with fuzz at the end of the numbers.

There really should only be 19 records, just not the 19 that you ended up with.

The following corrects your result by getting rid of the fuzz in the first step by taking the INT of the datetime:

libname test 'c:\test.xls'

     mixed=yes

     stringDates=yes

     scanTime=yes;

run;

data have;

  set test.'sheet1$'n(dbSasType=( time=dateTime ));

  format time datetime.;

  time=int(time);

run;

proc expand data=have out=temp to=minute method=none;

      id time;

run;

data want;

   set temp;

   if temp=. then temp=0;

run;

libname test clear;

[Linlin · Accepted solution]

Hi Art,

Thank you very much!!!

Hi James,

Below is my updated code.

libname test 'c:\test.xls'

     mixed=yes

     stringDates=yes

     scanTime=yes;

run;

data have;

    set test.'sheet1$'n(dbSasType=( time=dateTime ));

format time datetime.;

time=int(time);

run;

proc expand data=have out=want to=minute method=none;

      id time; 

run;

data want;

   set want;

   if temp=. then temp=0;

run;

proc print data=&syslast;

  title from dataset &syslast;

run;

libname test clear;

title;

Linlin

[jamescase]

Brilliant! Thank you, Art and Linlin. Both methods work perfectly for what I was hoping to accomplish!

all my best,

james

[art297]

James,

Glad to hear that you were able to solve your problem.  Please mark one of the responses as being the correct answer, or others will see the question as not having been answered and will continue to try to help.

I'd suggest Linlin's as he could definitely use the points more than I could and I, personally, liked his suggestion.

[Linlin]

Hi Art,

When you talk about me please use she or her.  Thank you!

[art297]

Oops!  Sorry!  I usually don't use gender specific comments in my posts as I never know who is a him or her.  Obviously, this time, rushing to get you some points for your post I didn't keep true to my usual standards.

Won't happen again .. unless I'm in a similar kind of rush!