Convert time interval from min to 5 min

[mitiksh]

I have this BTC data This is collected with a time interval of 1 min. I want to change this interval to 5 min. 

31MAR16:00:00:00

BITF

BTC/USD

413.04

413.27

413.04

413.27

8.9537465

31MAR16:00:01:00

BITF

BTC/USD

413.27

413.27

413.27

413.27

0

31MAR16:00:02:00

BITF

BTC/USD

413.27

413.27

413.27

413.27

0

31MAR16:00:03:00

BITF

BTC/USD

413.27

413.27

413.27

413.27

4.726186

Please help Thank you

[ballardw]

You need to show what the result of changing the interval will look like, especially how it affects the other variables.

I recommend any example include enough "start" records to create a minimum of 2 of the intervals you want.

Best is to provide example data in the form of a data step so we don't have to ask things like which variables may be character and other properties. Then with data we can actually write code with your variables (hint hint) and such.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

[mitiksh]

data WORK.BTC;
input time_start:DATETIME. exchange:$4. market:$7. open:BEST12. high:BEST12. low:BEST12. close:BEST12. volume:BEST12.;
format time_start DATETIME. open BEST12. high BEST12. low BEST12. close BEST12. volume BEST12.;
datalines;
31MAR16:00:00:00 BITF BTC/USD 413.04 413.27 413.04 413.27 8.9537465
31MAR16:00:01:00 BITF BTC/USD 413.27 413.27 413.27 413.27 0
31MAR16:00:02:00 BITF BTC/USD 413.27 413.27 413.27 413.27 0
31MAR16:00:03:00 BITF BTC/USD 413.27 413.27 413.27 413.27 4.726186
31MAR16:00:04:00 BITF BTC/USD 413.27 413.27 413.27 413.27 0
31MAR16:00:05:00 BITF BTC/USD 413.26 413.26 413.26 413.26 0.03515718
31MAR16:00:06:00 BITF BTC/USD 413.26 413.26 413.26 413.26 0
31MAR16:00:07:00 BITF BTC/USD 413.25 413.25 413.25 413.25 0.24729037
31MAR16:00:08:00 BITF BTC/USD 413.27 413.27 413.27 413.27 1.5
31MAR16:00:09:00 BITF BTC/USD 413.27 413.28 413.27 413.28 9.27629496
31MAR16:00:10:00 BITF BTC/USD 413.28 413.51 413.28 413.51 43.64005163
31MAR16:00:11:00 BITF BTC/USD 414.25 414.93 414.25 414.93 145.1566291
31MAR16:00:12:00 BITF BTC/USD 414.88 414.9 414.88 414.9 0.1309879
31MAR16:00:13:00 BITF BTC/USD 414.98 414.99 414.98 414.98 0.65589598
31MAR16:00:14:00 BITF BTC/USD 414.98 414.98 414.98 414.98 0.01226425
31MAR16:00:15:00 BITF BTC/USD 414.98 414.98 414.98 414.98 0.03889585
31MAR16:00:16:00 BITF BTC/USD 414.98 414.98 414.98 414.98 1.062
31MAR16:00:17:00 BITF BTC/USD 414.98 414.98 414.98 414.98 0.48398974
31MAR16:00:18:00 BITF BTC/USD 414.99 414.99 414.99 414.99 26.18839859
31MAR16:00:19:00 BITF BTC/USD 414.98 414.98 414.98 414.98 0.1
;

This will generate sample dataset.

time_start	exchange	market	open	high	low	close	volume
31MAR16:00:00:00	BITF	BTC/USD	413.04	413.27	413.04	413.27	8.9537465
31MAR16:00:05:00	BITF	BTC/USD	413.26	413.27	413.26	413.26	0.03515718
31MAR16:00:010:00	BITF	BTC/USD	413.28	413.51	413.25	413.51	43.64005163

This should be the desired output. 

we can think about of window of 5 min and then just consider the first and the last entry in the window. we can directly copy the open and close but for high and low we need to find the max of high column and low as min of low column from those 5 entries we considered. Hope this explanation is understandable. I am a beginner so please pardon.

[Reeza]

Do you have SAS ETS licensed? You can check with proc product_status;run;

If so, PROC EXPAND and TIMESERIES are designed to do these type of calculations quite easily.
Example
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.3/etsug/etsug_timeseries_examples01.htm
Full code for example:
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.3/etsug/etsug_code_tseex01.htm

[FreelanceReinh]

Hi @mitiksh,

---

@mitiksh wrote:

time_start	exchange	market	open	high	low	close	volume
31MAR16:00:00:00	BITF	BTC/USD	413.04	413.27	413.04	413.27	8.9537465
31MAR16:00:05:00	BITF	BTC/USD	413.26	413.27	413.26	413.26	0.03515718
31MAR16:00:010:00	BITF	BTC/USD	413.28	413.51	413.25	413.51	43.64005163

This should be the desired output. 

we can think about of window of 5 min and then just consider the first and the last entry in the window. we can directly copy the open and close but for high and low we need to find the max of high column and low as min of low column from those 5 entries we considered. Hope this explanation is understandable. I am a beginner so please pardon.

---

Wouldn't we also add the volumes that fall into a common 5-min window so that, e.g., the 4.726186 from 31MAR16:00:03:00 would be added to the 0.03515718 in your "desired output" table?

[ChanceTGardener]

Most likely a two-step process.

Step 1 ---> create a new datetime index with five minute intervals

Step 2 ---> use the new datetime index to aggregate columns appropriately

Questions regarding Step 1:

For the five minute interval 31MAR16:00:00:00, is this supposed to contain minutes 00, 01, 02, 03, and 04 on 31MAR16? Similarly, is 31MAR16:00:05:00 supposed to contain minutes 05, 06, 07, 08, and 09 on 31MAR16, and so on? If correct, the code below creates the new datetime index. If incorrect, please explain. 

data idx_create;
 set BTC;
  do i=0 to 55 by 5;
    dateidx = ifn(i<=minute(time_start)<=i+4,dhms(datepart(time_start),hour(time_start),i,0),dateidx);
  end;
 format dateidx datetime22.;
run;

With respect to Step 2, please explain what you're looking for in the following columns and use the new datetime index value 31MAR16:00:00:00 to talk through the logic for how each column should be calculated. 

High = maximum value of High column among the 5 observations in each new datetime index value

Low = minimum value of High column among the 5 observations in each new datetime index value

Exchange = ?

Market = ?

Open = ?

Close = ?

Volume = ?

[mitiksh]

Regarding

Question 1:

Consider this Example, 

31MAR16:00:05:00, this is supposed to contain minutes 1, 2, 3, 4 and 5 on 31MAR16. Similarly 31MAR16:00:10:00, This is supposed to contain minutes 6, 7, 8, 9 and 10.

Question 2:

Considering the new Date time index, New entry would look like this

31MAR16:00:00:00

high = Max_high (31MAR15:00:56:00, 31MAR15:00:57:00, 31MAR15:00:58:00, 31MAR15:00:59:00, 31MAR16:00:00:00)

Low = Min_low (31MAR15:00:56:00, 31MAR15:00:57:00, 31MAR15:00:58:00, 31MAR15:00:59:00, 31MAR16:00:00:00)

Open = (Open Value at 31MAR15:00:56:00)

Close = (Close Value at 31MAR16:00:00:00)

Volume = sum_volume ( 31MAR15:00:56:00, 31MAR15:00:57:00, 31MAR15:00:58:00, 31MAR15:00:59:00, 31MAR16:00:00:00)

exchange and market are same for all.

[ChrisNZ · Accepted solution]

Like this?

data WANT;
  set HAVE end=LASTOBS;
  if mod(minute(TIME_START),5)=1 | _N_=1 then do;
    call missing(_HIGH,_LOW,_VOLUME);
    _OPEN=OPEN;
  end;
  _LOW =min(LOW ,_LOW );
  _HIGH=max(HIGH,_HIGH);
  _VOLUME+VOLUME;
  if mod(minute(TIME_START),5)=0 | LASTOBS then output;
  retain _: ;
  drop OPEN HIGH LOW VOLUME ;
  rename _OPEN=OPEN _HIGH=HIGH _LOW=LOW _VOLUME=VOLUME ;
run;

[mitiksh]

Thank you @ChrisNZ. and every other contributor for help.