Time data

[STATSKING]

Hi,

I have timedate data on the usd/nzd spread, and i am trying to organise it a bit better.

Currently have 200k observations over a year, that measure price, etc. i already have the following columns already made. if its in the 00.00.00-00.59.59 hour of the day. returning as a 1. and so on. also same for minuties, and 5min intervals.

What i want to is sort the data that it gives me the last quote in a 5 min interval. or if there is no quote in that 5min interval to return the previous 5min intervals quote price.

Some 5min intervals have multiple quotes, others have none.

is there any simple way of re organising my data like this?

[ballardw]

Show a few records, dummy values are fine, as long as the key elements show the behavior you need, and what the final result might look like for that example data.

[PGStats]

Here is a simulated example

data test;
call streaminit(798687);
timedate = '20APR2016:00:00:00'dt;
format timedate datetime17.;
do obs = 1 to 100;
    timedate + rand("Poisson", '00:02:00't);
    if rand("uniform") > 0.3 
        then value = obs; 
        else call missing(value);
    output;
    end;
run;

data test2;
set test;
timedate5 = intnx("minute5", timedate, 1, "beginning");
format timedate5 datetime17.;
run;

data want;
set test2; by timedate5;
retain lastValue;
if last.timedate5 then do;
    if missing(value) 
        then value = lastValue;
        else lastValue = value;
    output;
    end;
drop lastValue;
run;

[PGStats]

Here is a better version that takes care of missing intervals:

/* Generate some random times with some missing values */
data test;
call streaminit(798687);
timedate = '20APR2016:00:00:00'dt;
format timedate datetime17.;
do obs = 1 to 100;
    timedate + rand("Exponential") * '00:03:00't;
    if rand("uniform") > 0.3 
        then value = obs; 
        else call missing(value);
    output;
    end;
run;

/* Assign values to 5 minute intervals. Add missing intervals */
data test2;
set test(rename=value=thisValue);
t = intnx("minute5", timedate, 1, "beginning");
do timedate5 = intnx("minute5", coalesce(lag(t),t), 1) 
        to intnx("minute5", t, -1) 
        by '00:05:00't;
    output;
    end;
timedate5 = t;
value = thisValue;
output;
format timedate5 datetime17.;
drop thisValue t;
run;

/* Keep last value in each interval. Carry values over to fill missing values */
data want;
set test2; by timedate5;
retain lastValue;
if last.timedate5 then do;
    if missing(value) 
        then value = lastValue;
        else lastValue = value;
    output;
    end;
drop lastValue;
run;