Find record with joining time greater than relieving time

[abraham1]

Hi All,
In my dataset, I want to retrieve only those records where emp joining time (hh:mm) > relieve time (hh:mm)

e.g I am not sure how to import dates into emp dataset. Please check.

data emp;
input id join relieve;
cards;
101 2022-11-24T09:21:04 2022-11-24T11:24:14
102 2022-12-09T07:19:10 2022-12-09T05:19:10
;
run;

data emp1;
set emp;
join1= substr(join,12,5);
jointime = timepart( intnx('dthour',input(join1,time5.),0,'same'));
relieve1= substr(relieve,12,5);
relievetime = timepart( intnx('dthour',input(relieve1,time5.),0,'same'));
format jointime relievetime time5.;
run;

data final;
set emp1;
where jointime>relievetime;
run;

[PaigeMiller · Accepted solution]

---

@abraham1 wrote:

Hi All,
In my dataset, I want to retrieve only those records where emp joining time (hh:mm) > relieve time (hh:mm)

e.g I am not sure how to import dates into emp dataset. Please check.

---

Comment: never work with dates and times as character strings. To do arithmetic or logical operations on dates and times (which you want to do, comparing join to relieve), always use numeric date, date/time or time values. To obtain numeric dates and times, you import the data using the proper INFORMAT when reading the data (in this case the informat is e8601dt.), which converts the date/times like 2022-11-24T09:21:04 into actual SAS numeric values.

data emp;
input id join :e8601dt. relieve :e8601dt.;
format join relieve e8601dt.;
cards;
101 2022-11-24T09:21:04 2022-11-24T11:24:14
102 2022-12-09T07:19:10 2022-12-09T05:19:10
;

data want;
    set emp;
    if timepart(join)>timepart(relieve);
run;

[abraham1]

Thank you sir for the details.

When the dates are stored in character format in dataset, how can this be handled.

I used below code but don't know how to pull data with joining time greater than relieving time. can you please help me one more time.

data emp;
input id join $20. relieve $20.;
cards;
101 2022-11-24T09:21:04 2022-11-24T11:24:14
102 2022-12-09T07:19:10 2022-12-09T05:19:10
;
run;

data emp1;
set emp;
join1= substr(join,12,5);
jointime = timepart( intnx('dthour',input(join1,time5.),0,'same'));
relieve1= substr(relieve,12,5);
relievetime = timepart( intnx('dthour',input(relieve1,time5.),0,'same'));
format jointime relievetime time5.;
run;

[Kurt_Bremser]

Storing dates or times as character is idiocy squared. It takes a LOT more space and deprives you of the use of all the tools SAS provides for such data.

If you can't go back to the original import process, fix the data first like this:

data emp_fixed;
set emp (rename=(join=_join relieve=_relieve));
join = input(_join,e8601ft19.);
relieve = input(_relieve,e8601dt19.);
format join relieve e8601dt19.;
drop _join _relieve;
run;

data emp1;
set emp_fixed;
where join gt relieve;
run;

[abraham1]

I am not getting expected result as I need to compare time in HH:MM between two dates. I have added one more record (103) where time 08:19 >05:11 irrepective of date. 

I am expecting this record also to be retrieved along with 101.

data emp;
input id join $20. relieve $20.;
cards;
101 2022-11-24T09:21:04 2022-11-24T11:24:14
102 2022-12-09T07:19:10 2022-12-09T05:19:10
103 2022-12-05T08:19:10 2022-12-09T05:11:10
;
run;
data emp_fixed;
set emp (rename=(join=_join relieve=_relieve));
join = input(_join,e8601dt19.);
relieve = input(_relieve,e8601dt19.);
format join relieve e8601dt19.;
drop _join _relieve;
run;
data emp1;
set emp_fixed;
where join gt relieve;
run;

[PaigeMiller]

I have already provided code that works.

[abraham1]

Thank you sir as both of you can help me to resolve my query.