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
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;
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.
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.
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?
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 = ?
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.
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.