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;
@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 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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.