Subsetting based on TIME

[robertrao]

Hi Team,

if its on the same day for the ID and the difference is within 5-6 hrs and the corresponding OUT or IN is missing then we need to discard the record........

HAVE

ID                          IN                                    OUT

101            02AUG2013:13:18                       . 

101            02AUG2013:14:25             03AUG2013:15:10

102           05AUG2013:16:25                   .

102           05AUG2013:19:04                    .

102           05AUG2013:23:00                    .

103           06AUG2013:13:55            06AUG2013:17:14

103           16AUG2013:17:02            17AUG2013:07:00

103           29AUG2013:1215                      .

103           29AUG2013:12:28                      .

104         07AUG2013:16:00              09AUG2013:14:55

104                   .                                 09AUG2013:16:05

105        10AUG2013:08:40                         .

105        10AUG2013:09:00              14AUG2013:10:30

105                   .                                14AUG2013:10:40

106        20AUG2013:18:49                        .

106        20AUG2013:18:50                         .

106        20AUG2013:18:54               21AUG2013:10:37

107        20AUG2013:08:27               28AUG2013:11:05

107                   .                                 28AUG2013:11:06

107        28AUG2013:13:40                         .

107        28AUG2013:14:00                         .

108        26AUG2013:10:17                        .

108        26AUG2013:10:30              30AUG2013:10:40

108                 .                                  30AUG2013:10:45

WANT:

ID                          IN                                    OUT

101           02AUG2013:14:25             03AUG2013:15:10

102          05AUG2013:23:00                    .

103          06AUG2013:13:55            06AUG2013:17:14

103          16AUG2013:17:02            17AUG2013:07:00

103          29AUG2013:12:28                      .

104         07AUG2013:16:00              09AUG2013:14:55

105        10AUG2013:09:00              14AUG2013:10:30

106        20AUG2013:18:54               21AUG2013:10:37

107        20AUG2013:08:27               28AUG2013:11:05

107        28AUG2013:14:00                         .

108        26AUG2013:10:30              30AUG2013:10:40

Thanks

[LinusH]

Since 6 hours is 6 * 60 * 60 = 21600 secs, so something like this:

out between in and (in + 21600)

If you want to calculate using full hours, you could look at the intck() and intnx() functions.

For the logic of last in, if many in without outs, you want to look at first., last. logic paired with retained variables. 

[mkeintz]

Why are these records in your WANT dataset?  They don't fit your description as I understand it.

1. 102 05AUG2013:23:00 .         ** This record is NOT more than 6 hours from its predecessor (102 05AUG2013:19:04 .).
2. 107 28AUG2013:14:00 .         ** Same issue (preceding record is 107 28AUG2013:13:40 .).

[Mit · Accepted solution]

I agree with you mkeintz.

@robertrao :Do you mean:

keep everything that has In and OUT.

delete all if OUT is there but no corresponding IN

fot all same dates where there is IN and no OUT then keep the latest IN and discard all other.

If the above logic is correct follow the following steps:

1. prepare two datasets from the original data - no1. all with IN and OUT

                                                                               no2. all with In and no OUT

2. then sort the no2. data in descending order of IIN and keep the first.IN  - this is your dataset no.3

3. now append no1 and no3.