building artificial time stamp and series

[ctse]

Hi,

I have the following table in sas with mydatetime, mydate, mytime and myreturn:

Mydate Mytime MyReturn
30Jan06 10:00:01 0.03
30Jan06 10:02:05 0.08
30Jan06 10:04:05 0.04
30Jan06 10:06:05 0.02
. . .
30Jan06 16:45:01 0.01
30Jan06 16:45:23 0.04
. . .
. . .
. . .
31Jan06 09:45:01 0.01
31Jan06 09:45:01 0.04
31Jan06 09:45:02 0.03

The table is huge and the dots just mean that the table continues.

I want to construct an artificial time stamp spaced 5 minutes equally starting from the start of trade at 10.00am which picks the returns closest to the 5 mins.

That is, for the 30Jan06, i want a table like:

Mydate Mytime(5) MyReturn
30Jan06 10:00:01 0.03
30Jan06 10:05:01 0.04
etc

Thank you,
c+

 

[klaz2002]

You need to have a begin datetime and then categorize each observation into a 5 minute flag value. I call this blocking. You take the original datetime value and check the current datetime against that. Take the number of minutes and divide by 5 to get a value for your five minute block number. Then take the last record in each of these blocks. (This is only if you want the closest record that occurs before the five minute end point. If you wanted the closest record that occurs at the five minute end point regardless of if it occurred before or after said end point, you will need to do something else.)

proc sort 
   data = your_data
   out  = test_sorted;
   by MyNewSASDate timestamp;
run;

data test_proc;
   set test_sorted;

   by MyNewSASDate;  *** THIS DATE IS TAKEN FROM YOUR TEXT AND CONVERTED TO A SASDATE VALUE ***;
   retain origdatetime;
   if first.MyNewSASDate then do;
      origdatetime = timestamp;
   end;
   fiveminblockno = int(((timestamp - origdatetime)/60)/5);
   format  origdatetime  timestamp datetime18.;
run;

proc sort 
   data = test_proc
   out  = final_data;
   by MyNewSASDate  fiveminblockno timestamp;
run;

data final;
  set final_data;
  by  MyNewSASDate  fiveminblockno timestamp;
  if last.fiveminblockno;
run;

I hope you get the picture.

Klaz

 

[ctse]

Thanks Klaz.

You've done it again!