topic Re: Find record with joining time greater than relieving time in SAS Programming

Find record with joining time greater than relieving time

abraham1 — Thu, 02 Mar 2023 18:15:17 GMT

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;

Re: Find record with joining time greater than relieving time

PaigeMiller — Thu, 02 Mar 2023 18:36:33 GMT

@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;

Re: Find record with joining time greater than relieving time

abraham1 — Thu, 02 Mar 2023 19:18:08 GMT

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;

Re: Find record with joining time greater than relieving time

Kurt_Bremser — Thu, 02 Mar 2023 20:06:57 GMT

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;

Re: Find record with joining time greater than relieving time

abraham1 — Fri, 03 Mar 2023 01:37:14 GMT

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;

Re: Find record with joining time greater than relieving time

PaigeMiller — Fri, 03 Mar 2023 02:08:34 GMT

I have already provided code that works.

Re: Find record with joining time greater than relieving time

abraham1 — Fri, 03 Mar 2023 02:21:29 GMT

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