Occurence between intervals 1

[ctse]

Hi all,

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

Mydate Mytime MyReturn
30Jan06 16:45:01 0.01
30Jan06 16:45:23 0.08
. . .
. . .
30Jan06 17:00:01 0.09
. . .
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.


How can I ask SAS to remove observations that have a myReturn greater than 0.05 AND that occurred within a 30 minutes interval BEFORE AND AFTER the event.

That is, in the above table, I want the transaction occurring on the 30Jan06 at 16:45:01 and 17:00:01 to be removed because (1) myReturn >0.05 AND (2) they fall within a 30 minutes interval of each other. (note: the return at 17:00:01 occurs about 15 mins after whereas the return at 16:45:01 occurs about 15 minutes before)

Many Thanks,
c+

 

[klaz2002]

You need to think of the transactions that have the high MyReturn value (>0.05) as anchors. These transaction will be tagged by you and you will use a RETAIN statement to eliminate both events that occur before and after in a 30 minute window. First you'll tag those records that have a high value. Then you will sort the data in using the date and time to set the data chronologically. Use the retain statement to bring the anchor time down across obs, do the math (30 minute window) eliminate those records. Then resort using the DESCENDING keyword and repeat the excersize. This will allow you to 'go' both ways.

ex

proc sort
  data = yourdata
  out  = yourdatasorted;
  by youdatetimevalue;
run;

data taggedinfo;
  set yourdatasorted;
  if myreturn gt .5 then anchor = 1;
run;
data yourrange;
  set yourdatasorted;
  retain anchordate;
  if anchor  = 1 then 
     anchordate = datetimevalue;
  mins_diff = abs((timepart(anchordate)-timepart(datetimevalue))/60);
if mins_diff lt 30 then
   flag=1;
run;
proc sort
  data = yourrange
  out  = ydata;
  by descending yourdatetimevalue;
run;

data yourrange2;
  set ydata;
  retain anchordate;
  if anchor  = 1 then 
     anchordate = datetimevalue;
  mins_diff = abs((timepart(anchordate)-timepart(datetimevalue))/60);
if mins_diff lt 30 then
   flag2=1;
run;
data final;
  set yourrange2;
  if myreturn gt.5 and flag=1 and flag2=1 then DELETE;
run;

This should work. I couldn't test it yet, but I hope you get the concept of how to do this.

Klaz

 

[ctse]

That's terrific thinking and it works!
Thank you,
c+