How to divide time period into non-equal intervals?

[aminkarimid]

Hello everybody

I want to divide time variable into intervals which are described below:

First half an hour: 9:00 << Time < 9:30;
Second half an hour: 9:30 << Time < 10:00;
Third half an hour: 10:00 << Time < 10:30;
Fourth half an hour: 10:30 << Time < 11;
Fifth half an hour: 11:00 << Time < 11:30;
Sixth half an hour: 11:30 << Time << 12;

Please pay attention to last interval. It is a closed interval.

Here is a sample of my dataset:

data WORK.SAMPLEDATA01;
  infile datalines dsd truncover;
  input TRD_STCK_CD:$15. TRD_PR:32. TRD_TUROVR:14. TRD_EVENT_DT:DATE9. TRD_EVENT_TM:TIME5.;
  format TRD_TUROVR 14. TRD_EVENT_DT DATE9. TRD_EVENT_TM TIME5.;
  label TRD_STCK_CD="TRD_STCK_CD" TRD_PR="TRD_PR" TRD_TUROVR="TRD_TUROVR" TRD_EVENT_DT="TRD_EVENT_DT";
datalines4;
IKCQ1,1,100,24MAR2008,11:19
ALBZ1,1537,10000,24MAR2008,12:28
ALBZ1,1567,10,24MAR2008,13:13
AZAB1,683,10000,24MAR2008,12:20
AZAB1,695,10,24MAR2008,13:13
BALI1,850,9260,24MAR2008,9:14
BALI1,850,2000,24MAR2008,9:15
BALI1,850,10000,24MAR2008,10:15
BALI1,850,6000,24MAR2008,11:15
BALI1,850,10000,24MAR2008,11:29
BALI1,850,10000,24MAR2008,12:00
BALI1,850,10000,24MAR2008,12:28
BALI1,865,10,24MAR2008,13:13
BANK1,1164,10729,24MAR2008,8:38
BANK1,1148,2000,24MAR2008,11:24
BANK1,1147,1575,24MAR2008,11:24
BANK1,1147,5000,24MAR2008,11:40
BANK1,1147,3425,24MAR2008,11:51
BANK1,1141,41575,24MAR2008,11:59
BANK1,1141,8425,24MAR2008,11:59
BANK1,1141,30000,24MAR2008,12:00
BANK1,1162,10,24MAR2008,13:13
BHMN1,1013,1500,24MAR2008,8:00
BHMN1,1013,1500,24MAR2008,9:00
BHMN1,1013,1500,24MAR2008,9:01
BHMN1,1013,1500,24MAR2008,9:34
BHMN1,1013,1500,24MAR2008,10:05
BHMN1,1013,1500,24MAR2008,10:59
BHMN1,1013,1000,24MAR2008,11:29
BHMN1,1013,450,24MAR2008,12:00
;;;;

How can I do that?

Thanks in advance.

[Reeza · Accepted solution]

Use a Format. 

http://www2.sas.com/proceedings/sugi30/001-30.pdf

[Shmuel]

Just to expand @Reeza's answer:

By creating a format you can assign a code/order number to each sub period and add it to your data

in order to enable easier analyse:

proc format lib=work;
   vaue tm_code
      '09:00't - '09:30't = '1'
      '09:30't - '10:00't = '2'
     ... etc. ...
      '11:30't - '12:00't = '6'
     other                = '9'
; run;

data WORK.SAMPLEDATA01;
  infile datalines dsd truncover;
  input TRD_STCK_CD:$15. TRD_PR:32. TRD_TUROVR:14. 
          TRD_EVENT_DT:DATE9. TRD_EVENT_TM:TIME5.;
  format TRD_TUROVR 14. TRD_EVENT_DT DATE9. TRD_EVENT_TM 
         TIME5.;
  TM_CODE = input(put(TRD_EVENT_TM, tm_code.),1.);   /* <<<<< line added <<< */
  label TRD_STCK_CD="TRD_STCK_CD" TRD_PR="TRD_PR" TRD_TUROVR="TRD_TUROVR" TRD_EVENT_DT="TRD_EVENT_DT";
datalines4;
... your data ...
; run;

[ballardw]

And what do you want done with values out side of the specified times?

Your example data has 13:13, 12:20, 12:28. So what should be done with these?

[aminkarimid]

These are outliers and should be deleted from sample like using WHERE statement.

[Reeza]

@aminkarimid If it's not relevant to the question at hand, it's helpful to not include them in your sample data. 

[aminkarimid]

Yes, you are right @Reeza, and I am sorry about that.

[Reeza]

I think this questions been answered, so please mark it as such when you have some time. Thanks.

[aminkarimid]

I did not get the answer. Maybe my question is ambiguous. I want to round up time in specific time interval which I described them before. But the problem is, when I round up last interval, the 12 O'clock is considered as a 12:30. On the other word the rounding interval is a half-open interval and I want change it to closed interval for last interval.

[Reeza]

Proc Format allows you to control that, the inclusion/exclusion of end points within the declaration. 

Post your non-working code. Your question is not ambiguous, it's relatively simple and a beginner should be able to piece this together IMO using the basic paper I linked to and potentially referencing the documentation on the inclusion/exclusion if not covered explicitly in the paper.