It appears that there are no "holes" in your series. I.e. every minute is recorded, so a five-minute lead is always a 5-record lead.
This makes it simple - a 3-statement program as below. You just have to use the MERGE statement inside a DATA step. This program merges HAVE with two copies of itself. But one copy is offset by 1 observation (that's the FIRSTOBS=2 parameter below) and the other copy is offset by 5 obs (firstobs=6). Note the default is firstobs=1. So as a result you are reading in parallel
obs1, obs2, obs6
then
obs2, obs3, obs7
obs3, obs4, obs8
etc.
The only problem is that each copy of HAVE has the same variables (bbp in your case), but you can't have 3 values simultaneously occupying one var. So the second and third copies of have use the "keep=bbp" parameter to just read in just one var, and also a "rename=" parameter to make sure each merged copy of dataset HAVE has unique var names. If you have several variables (say you also have bbsize, bop, bosize), then make sure to keep them all and rename them all.
data have;
input DTM1 datetime. BBP;
format dtm1 datetime20.;
datalines;
03JUN2013:09:15:00 164.6
03JUN2013:09:16:00 167
03JUN2013:09:17:00 166
03JUN2013:09:18:00 164.15
03JUN2013:09:19:00 176.1
03JUN2013:09:20:00 169.3
03JUN2013:09:21:00 165
03JUN2013:09:22:00 180.7
03JUN2013:09:23:00 170
03JUN2013:09:24:00 180.3
03JUN2013:09:25:00 176
03JUN2013:09:26:00 175.5
03JUN2013:09:27:00 165.1
03JUN2013:09:28:00 164.3
03JUN2013:09:29:00 170
run;
data want;
merge have
have (firstobs=2 keep=bbp rename=(bbp=bbp1))
have (firstobs=6 keep=bbp rename=(bbp=bbp5));
run;
BTW, what's bbp? "best bid price"?
Now you probably have these time series for several stock symbols. Let's say your data is sorted by symbol/dtm1, not just dtm1 as above Then you need a way to prevent the last few records of one symbol from getting "leads" from the first few records of the second symbol. The logic below is to get lead values of symbol as well as bbp. Then if the lead value of symbol does not match current symbol, then set the corresponding bbp to missing:
data want (drop=_:);
merge have
have (firstobs=2 keep=symbol bbp rename=(symbol=_symbol1 bbp=bbp1))
have (firstobs=6 keep=symbol bbp rename=(symbol=_symbol5 bbp=bbp5));
if _symbol5^=symbol then do;
bbp5=.;
if _symbol1^=symbol then bbp1=.;
end;
run;
Note the "drop=_:" parameter tells sas not to output any vars whose name begins with the underscore.
Also note nesting of the
"if _symbol1^=symbol then bbp1=.;"
statement inside a do group is executed only "if _symbol5^=symbol then do;" After all, there's no need to compare _SYMBOL1 against SYMBOL if _SYMBOL5 matches SYMBOL.
Caveat: Some sas users might initially think to use MERGE with a BY statement, as in
merge have have (firstobs=2 keep=symbol bbp rename=(bbp=bbp1)) have (firstobs=6 keep=symbol bbp rename=(bbp=bbp5));
by symbol;
The problem is that the one-record and 5-record offsets would be preserved for the first symbol, but the moment the second symbol is started the they will no longer be offset as all. Thus you need the slightly tedious coding above to check SYMBOL against _SYMBOL1 and _SYMBOL5, etc.
If you have irregular time series (i.e. holes in the time points), you'll have to give up on the merge statement, and replace it with a collection of SET statements.
