making tick-by-tick data into time interval data

[LzEr23]

Dear all,
I asked similar question before but I had to post it again cause I needed further help this.

I have a tick-by-tick data looking like this:

data work.item_1;
infile datalines dsd truncover;
input date: $8. code: $6. datetime: 20;
datalines;
20180101 AAAAAA 01JAN2018 09:00:00.000
20180101 AAAAAA 01JAN2018 09:03:25.127
20180101 AAAAAA 01JAN2018 09:12:30.250
20180101 AAAAAA 01JAN2018 09:12:37.776
20180101 AAAAAA 01JAN2018 09:14:10.233
;;;;

This is one example of several datasets I have.
above is the first 5 obs, but the actual data are much longer and of many dates.
And the last column (datetime) is later formatted with datetime30.3 format.

I want to turn this dataset into a 5 min interval dataset
SO THAT the first observation would be 2
and the second observation be 0
and the third observation be 3.

I was able to make 5min interval data but I couldnt make the sas to count the second to be 0 since there is no data b/w 09:05:00 and 09:10:00.
It would help me a lot if I can get some help on this.

[Amir]

Hi,

Thanks for trying to supply the input data as a data step, however it needed amending to create a data set that holds the data being read, e.g.:

data work.item_1;
   infile datalines truncover;
   input date: yymmdd8. code: $6. datetime datetime22.3;

   format date     yymmddn8.
          datetime datetime22.3
   ;

   datalines;
20180101 AAAAAA 01JAN2018 09:00:00.000
20180101 AAAAAA 01JAN2018 09:03:25.127
20180101 AAAAAA 01JAN2018 09:12:30.250
20180101 AAAAAA 01JAN2018 09:12:37.776
20180101 AAAAAA 01JAN2018 09:14:10.233
;

I had trouble understanding the requirements, so would you please also create a data step showing the output data set you require, based on the input, thanks.

Regards,

Amir.

[LzEr23]

I'm sorry. I'm quite new with sas, and I don't think I properly understood what you mean by 'data step showing the output data set I require.'
The data step you amended above is correct, and I don't see what exactly is it that you are asking?

[Amir]

Hi,

You have created a data step to show your input data. I am asking that you create another data step that shows the output data that you want to see.

The principle will be the same as you coded in your question, just use similar code with new lines of data so that another data set (table) is created based on your data step (SAS code).

Hope that's clearer, if not, just ask.

Regards,

Amir.

[LzEr23]

Thank you for the quick reply.

I'm still not sure about what you're asking. But above would be something similar to what I want to get as an output set.

I hope this would be informative.

If I got it wrong, please tell me.

data work.item_output;
   infile datalines truncover;
   input interval: $8. volume;

   ;
   datalines;

01010900 2

01010905 0

01010910 3
; 

[Amir · Accepted solution]

Hi,

Thank you. I think I understand, you want to count how many observations fit in every five minute interval.

I coded the following which just works with the data provided, but I think it might need amending to handle all cases:

data want(keep = boundary1 volume rename = boundary1 = interval);
   format boundary1 boundary2 datetime22.3;

   retain volume    0
          boundary1 '01JAN2018 09:00:00.000'dt
          boundary2 '01JAN2018 09:05:00.000'dt
   ;

   set item_1 end = last_obs;

   if boundary1 le datetime lt boundary2 then
      volume + 1;
   else
   do;
      do while(not (boundary1 le datetime lt boundary2));
         output;
         volume    = 0;
         boundary1 = boundary2;
         boundary2 = boundary2 + '00:05't;
      end;
      volume = 1;
   end;

   if last_obs then
      output;
run;

Edit: Tweaked code.

Regards,

Amir.

[LzEr23]

Thank you so much!!

I changed some bits to suit my data,

but this just works perfectly, and gave exactly what I hoped for.

Thank you again for your help.

Have a really nice day.