topic Re: Time data in SAS Programming

Time data

STATSKING — Wed, 20 Apr 2016 22:54:54 GMT

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?

Re: Time data

ballardw — Wed, 20 Apr 2016 22:57:38 GMT

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.

Re: Time data

PGStats — Thu, 21 Apr 2016 02:05:30 GMT

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;

Re: Time data

PGStats — Thu, 21 Apr 2016 03:00:41 GMT

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;