Hi there, have a set of trading data that has observations (timed in milliseconds from midnight) but infrequent. To make it a reasonable timeseries I did a do loop to build a grid (one observation per second should be the outcome). Now I want my dataset to fit into the grid (summing up the volumes that happened in that second. I am very thankful for any ideas. See exampel and faulty code below. Grid: Seconds 3300100 3300200 3300300 . .
Data example: ID date miliseconds volume
ABC 01/01/15 3300214 100
ABC 01/01/15 3300300 200
ABC 01/01/15 3300415 300
ABC 01/01/15 4400122 400
ABC 01/01/15 4500022 200
ABC 01/01/15 4500111 400 Output: ID date seconds volume
ABC 01/01/15 3300100 100
ABC 01/01/15 3300200 500
ABC 01/01/15 3300300 400
ABC 01/01/15 3300400 600 ( time is miliseconds from midnught (just a numerical example that is not correct in terms of summing up) data grid;
do seconds = '09:10't to '16:50't ;
output ;
end ;
run;
proc sql ;
create table want as
select a.id, a.date, a.miliseconds, sum(a.volume) as Volume
from have a
left join
grid1 b on a.id = b.id
and a.date = b.date
and a.miliseconds = floor(b.seconds)
group by a.id, a.date, a.miliseconds ;
quit;
run; Unfortunately does that not work the way I want. Anything I'm missing? So, ID and date couldn't be found, so I tried to create them in the data step. But then ID isn't the same format, which I tried to fix but failed. It seems I have an error when merging the have dataset to the want by matching the miliseconds between the seconds gridlines. Always thankful for input! Best
... View more
