BookmarkSubscribeRSS Feed
STATSKING
Calcite | Level 5

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?

3 REPLIES 3
ballardw
Super User

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
Opal | Level 21

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;
PG
PGStats
Opal | Level 21

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;
PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 824 views
  • 1 like
  • 3 in conversation