Help using Base SAS procedures

Subsetting based on TIME

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,040
Accepted Solution

Subsetting based on TIME

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

Accepted Solutions
Solution
‎10-08-2013 10:53 PM
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Subsetting based on TIME

I agree with you mkeintz.

@robertrao Smiley Very Happyo 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.

View solution in original post


All Replies
Super User
Posts: 5,254

Re: Subsetting based on TIME

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. 

Data never sleeps
Valued Guide
Posts: 797

Re: Subsetting based on TIME

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 .).
Solution
‎10-08-2013 10:53 PM
Frequent Contributor
Frequent Contributor
Posts: 83

Re: Subsetting based on TIME

I agree with you mkeintz.

@robertrao Smiley Very Happyo 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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 211 views
  • 6 likes
  • 4 in conversation