topic Subsetting based on TIME in SAS Procedures

Subsetting based on TIME

robertrao — Sun, 06 Oct 2013 00:36:28 GMT

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

Re: Subsetting based on TIME

LinusH — Sun, 06 Oct 2013 13:24:52 GMT

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.

Re: Subsetting based on TIME

mkeintz — Tue, 08 Oct 2013 13:48:18 GMT

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

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

Re: Subsetting based on TIME

Mit — Wed, 09 Oct 2013 02:53:44 GMT

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.