topic Re: How to locate a record in a time series that is 'x' minutes ahead in time? in SAS Procedures

How to locate a record in a time series that is 'x' minutes ahead in time?

machete — Thu, 24 Jul 2014 15:44:07 GMT

Dear all,

I have a financial time series dataset with a frequency of seconds.

To perform statistical analysis I need to search and locate for every record (row), the record which is 5minutes ahead of time.

Then i would like to retain from the record which is 5minutes ahead two observations, the columns bidprc and offerprc and add then next to the original record.

For example if we take from my attached data:

time currency bidprc offerprc dt bidprc5min offerpc5mins

08:07:09 dem/chf 0.86760 0.86770 09JAN97:08:07:09 to be located to be located

I would like to locate above the record which is in at 08:12:09 or if no such record exists the one which is closest to that time.

Is it possible to perform the above search without creating two datasets and e.g using proc sql which is efficient and quick?

Any ideas on how to program the above are more than welcome

Thanks!!!

Neo

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

ballardw — Thu, 24 Jul 2014 15:59:51 GMT

Does the data ALWAYS have a record at exactly +5 minutes (300 seconds)?

If so something like this may work

proc sql;

create table newdata as

select a.*, b.bidprc as bidprc5min, b.offerprc as offerprc5min

from have as a left join have as b

on a.time = (b.time -300);

quit;

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

machete — Thu, 24 Jul 2014 16:29:05 GMT

Hi Ballardw,

I will check if the above works. However there isnt a record always at 300 seconds and I would like to make sure that there is always a record in the new tables, so it would be best if the code you proposed can locate the next closest record e.g in 301 seconds or more....

Is it possible to do this?

Thnx

Neo

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

machete — Fri, 25 Jul 2014 12:16:19 GMT

The code you provided generates additional records. The sample has 50000 but the newdata has 68350...any ideas why is that?

also if i run the code on my whole dataset (>1 million records) i get many empty ones, where values have not been located. is it possible to change the code as below so that i can get results for all my records?

on a.time >= (b.time - 300)

thanks again!

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

Ksharp — Fri, 25 Jul 2014 13:24:54 GMT

if you want the next closed record ,why not fetch the next obs ? if your table has already been sorted by DT .

proc import datafile='c:\temp\fixing sample.xls' out=have dbms=xls replace;run;

data want;
merge have have(firstobs=2  keep=bidprc offerprc rename=(bidprc=bidprc5min offerprc=offerprc5min));
label  bidprc5min=' '  offerprc5min=' ';
run;

Xia Keshan

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

machete — Mon, 28 Jul 2014 08:56:25 GMT

Hi Xia,

the task is to find the record which is 5min ahead in time or if there is no such record then the one which is closest to 5mins....

The code that Ballardw provided was simply an inner join which works only if the data have ALWAYS a matchning record....which is NOT the case so I need some kind of an sql search algorithm...

Any ideas will help

Thnx

Neo

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

RW9 — Mon, 28 Jul 2014 09:21:47 GMT

Can you assign each row to a grouping variable, for instance actual time vs planned timepoint.

HAVE

ACTUAL_TIME BASE_TIME GROUP_ASSIGNED GROUPN

12:34 12:34 BASELINE 1

12:36 12:34 BASELINE+5 2

12:37 12:34 BASELINE+5 2

12:43 12:34 BASELINE+10 3

12:47 12:34 BASELINE+15 4

...

You can then group data into groups using the new variable irrespective of actual time, i.e;

select FIRST.ACTUAL_TIME,

NEXT.ACTUAL_TIME

from HAVE FIRST

left join HAVE NEXT

on FIRST.GROUPN=NEXT.GROUPN-1

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

jakarman — Mon, 28 Jul 2014 09:31:14 GMT

Can you change your logic?

It is a bit of strange thinking for future events that has happened already. It could be more easier thinking on calculations that have been happened in the paste.

That is more like stream processing. In that case the whole issue could be easier technical to solve (lag/retain) in am more generic way.
Validations on predictions (modeling) is a possible reason for your question.

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

gergely_batho — Mon, 28 Jul 2014 11:38:41 GMT

A trick used here: Merging two datasets with different but similar columns helps here.

Dataset should be sorted by time.

data have_minus5 / view= have_minus5;

set have;

time=time-'00:05:00't;/*or use intnx()*/

rename bidprc=bidprc5min offerprc=offerpc5mins;

run;

data want;

set have_minus5(in=inH5 keep=time) have(in=inH keep=time);

by time;

if inH then do;

set have;

output;

end;

if inH5 then do;

set have_minus5;

end;

run;

Sorry, this is not tested. Tested. And yes, this is like stream processing. ~ one pass through the data.

Be careful when copying apostrophes from this page, they are converted to something else.

I have changed this set statement in the code: set have(in=inH keep=time) have_minus5(in=inH5 keep=time);

It matters which dataset comes first. By changing dataset order in this set statement, you have a very fine control, over handling equal timestamps.

Message was edited by: Gergely Bathó

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

Ksharp — Mon, 28 Jul 2014 14:18:41 GMT

Here is . But it cost me two minutes to run it. Let me know if it could worked.

proc import datafile='c:\temp\fixing sample.xls' out=have dbms=xls replace;run;

proc sql;
create table want as
 select a.*,(select bidprc from have where time ge a.time+300 having time eq min(time) ) as bidprc5min
   , (select offerprc from have where time ge a.time+300 having time eq min(time) ) as offerprc5min
  from have as a ;
quit;

Xia Keshan

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

machete — Fri, 01 Aug 2014 07:50:07 GMT

Hi,

well I am nost sure how this will work as the orginal dataset will have 20million rows...re arranging the whole dataset into groups will be difficult...a search algorithm will work easier however I dont know how to program it....

Neo

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

machete — Fri, 01 Aug 2014 07:51:42 GMT

i am doing both looking in the past but also tryng to 'validate' trading strategies...this is why I want to 'fetch' the records which are ahead (leading) in time fox x minutes

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

jakarman — Fri, 01 Aug 2014 08:04:47 GMT

If you can validate your strategies when processing those future records as the current one you do not need any reordering, it will be kept as stream-processing.

Just think in an other way to solve time questions. Suppose you have 100 strategies calculated. Evaluation can be done later (log/retain) not necessary at you current record.

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

gergely_batho — Fri, 01 Aug 2014 08:12:35 GMT

Hi Neo, I realy suggest you to try the above mentioned approach. It runs very fast: 0.04 sec on the attached data. It needs only one pass through the data. 20mill rows will be not a problem.

With some more programming it can be extended to support different lookahead and lookback times. Or to support different currencies or more trading days.

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

machete — Fri, 01 Aug 2014 14:10:39 GMT

Hi Gergely,

by above you mean the one you proposed?

I will run it and let you know, however by looking at the code I am almost sure that some records will be empty/unmatched since the records are not at regular intervals, it means there might be no record exactly at 5min ahead but 5minutes and 5 seconds and the code will need to check is there is no record to pick the next closest,in this case at 5mins and 5 seconds....I will let you know as soon as I check yours

Neo

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

machete — Fri, 01 Aug 2014 16:57:04 GMT

Hi Xia,

i tried your code but it does not work, any ideas why?? by reading it looks ok but it does not execute:

15 proc sql;

16 create table fixing.fixing_eurchfsample1want as

17 select a.*,(select bidprc from fixing.fixing_eurchfsample1 where time ge a.time+300 having

17 ! time eq min(time)) as bidprc5min,

18 (select offerprc from fixing.fixing_eurchfsample1 where time ge a.time+300 having time eq

18 ! min(time)) as offerprc5min

19 from fixing.fixing_eurchfsample1 as a ;

NOTE: The query requires remerging summary statistics back with the original data.

ERROR: Subquery evaluated to more than one row.

NOTE: Correlation values are: time=67400 .

20 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.45 seconds

cpu time 0.43 seconds

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

PGStats — Fri, 01 Aug 2014 18:47:17 GMT

Finding the nearest match is always a bit tricky. I set up a time window to find the nearest match between t+120 and t+480 seconds.

The following works OK but is a bit slow. It might be a bit faster if dt was indexed :

libname xl Excel "&sasforum.\datasets\fixing sample.xls" access=readonly;

proc sql;

create table s as

select input(dt, datetime20.) as dt format=datetime21.,

bidprc, offerprc from xl.'Sheet1$'n;

quit;

libname xl clear;

proc sql;

create table want as

select

a.bidprc, a.offerprc, a.dt,

b.bidprc as bidprc5,

b.offerprc as offerprc5,

b.dt as dt5,

abs(intck('SECOND', a.dt, b.dt) - 300) as toff

from

s as a inner join

s as b

on b.dt between a.dt+120 and a.dt+480

group by a.dt

having calculated toff = min(calculated toff);

quit;

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

jakarman — Fri, 01 Aug 2014 22:17:44 GMT

The question as asked is not one for the SQL an not for analytics approaches.

Checking the sample dataset the timing with the events is very random at several second to minutes and longer.

As human you would put your finger on the line searching for the record you need an put new values in.
This a "balance line merge" algorithm known from 3 GL programming languages.

The dedicated problem is:

- merging the dataset to is self

- assuming the data is sorted (dtcur)

The find condition is not very sophisticated the difference is 5 minutes except for the last records.

Once working this one could be improved.

Using the point option with the set statement moving using directs access in the dataset is possible.

Executing will be fast as long the cache on IO can solve all request (in memory).

Other techniques as sasfile or the hashobject are options with other limitations.

/* create testdata , this is from excel using European conventions */

filename fixinp "/folders/myshortcuts/ikke" ;

data fixings ;
infile fixinp(fixingsample.csv) dlm=";" dsd firstobs=2 truncover ;
input date : mmddyy. time : time. currency : $8. bidprc : numx. offerprc : numx. dtcur : datetime. ;
drop date time ;
eventid = _N_ ;
format dtcur datetime. ;
run;

/* the set combine/merge using point wiht searching */

data fixings300;
set fixings nobs=fn_nobs ;
Retain eventlk 1 ; check=1 ;
do while (eventlk < fn_nobs & check ) ;
set fixings (rename=(bidprc=bidprc5 offerprc=offerprc5m eventid=event5m dtcur=dtcur5m) ) point=eventlk end=end5m ;
if ( dtcur+300 > dtcur5m ) then eventlk=eventlk+1 ; else check=0;
end;
run;

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

PGStats — Sat, 02 Aug 2014 03:14:21 GMT

Much faster solution along the same lines as Jaap. The time window from 0 to 10 minutes is searched for the best match. If no observation occurs in that window, there is no match (missing values are generated)

libname xl Excel "&sasforum.\datasets\fixing sample.xls" access=readonly;

proc sql;

create table have as

select input(dt, datetime20.) as dt format=datetime21.,

bidprc, offerprc from xl.'Sheet1$'n;

quit;

libname xl clear;

/* Number of seconds in the future to seek */

%let matchGoal=300;

/* Maximum number of seconds away from the goal to tolerate*/

%let matchLimit=300;

data want;

set have nobs=nobs;

match = &matchLimit;

call missing(p5, dt5, bidprc5, offerprc5);

do p = _n_+1 to nobs by 1 until (m > match);

set have(keep=dt rename=dt=_dt) point=p;

m = abs(intck('SECOND', dt, _dt) - &matchGoal);

if m < match then do;

match = m;

p5 = p;

end;

if not missing(p5) then do;

set have(keep=dt bidprc offerprc

rename=(dt=dt5 bidprc=bidprc5 offerprc=offerprc5))

point=p5;

end;

keep dt bidprc offerprc dt5 bidprc5 offerprc5;

run;

Re: How to locate a record in a time series that is 'x' minutes ahead in time?

Ksharp — Sat, 02 Aug 2014 06:38:37 GMT

That is because there are duplicated rows for the same date and same time . E.X

01jan2010 00:08:21 21

01jan2010 00:08:21 54

proc import datafile='c:\temp\fixingsample.xls' out=temp dbms=xls replace;run;

proc sort data=temp out=have nodupkey;by date time;run;

proc sql;

create table want as

select a.*,(select bidprc from have where time ge a.time+300 having time eq min(time) ) as bidprc5min

, (select offerprc from have where time ge a.time+300 having time eq min(time) ) as offerprc5min

from have as a ;

quit;

Xia Keshan