Making the year part in two date/time fields equal

[Ihsan-Mahdi]

Hello,

I have a data set with two date/time variables (leave date/time and arrive date/time). In a couple of hundred records the YEAR part in these two variables is different in the same record, which should not be the case. Is there a way to make the YEAR part equal in both fields in each record without having to do it separately for each record at a time? Below is a sample of the data:

data sample;
INPUT ID LeaveDT datetime20. ArriveDT datetime20. ;
DATALINES;
1 29JAN2016:18:17:00 29JAN2017:01:30:00
2 25MAR2016:15:39:00 25MAR2017:16:54:00
3 23NOV2014:20:23:00 23NOV2019:20:28:00
4 10SEP2018:18:39:00 10SEP2019:18:54:00
5 08FEB2017:20:24:00 08FEB2018:20:47:00
;
RUN;

Thank you!

[arthurdpereira · Accepted solution]

Sure! You can fix this for the entire dataset in one step, without having to update each record manually. The idea is to make the YEAR part of 'ArriveDT' the same as the YEAR from 'LeaveDT', but keep the original month, day, and time from 'ArriveDT'.

Here's how you can do it in SAS:

data fixed;
  set sample;
  leave_year = year(LeaveDT);
  ArriveDT_fixed = dhms(
    mdy(month(ArriveDT), day(ArriveDT), leave_year),
    hour(ArriveDT), minute(ArriveDT), second(ArriveDT)
  );
  format ArriveDT_fixed datetime20.;
run;

• dhms(date, hour, minute, second) rebuilds a datetime value.
• This code takes the year from LeaveDT and applies it to ArriveDT, keeping everything else (month, day, hour, etc.) as it was.

That way, both fields will have the same year in each record, and you don't have to fix anything by hand.
Let me know if you need more details!

[Ihsan-Mahdi]

Thank you!

[Tom]

Which comes first LEAVE or ARRIVE?  Are we talking about a trip where you leave before you arrive?  Or hospital stays where you leave after you arrive?

What is the logic for deciding which variable to change?

What is the logic for deciding when having a different year is a problem. What if they arrived on Dec 31 and left on Jan 1?  What if the duration was actually more than 365 days?

What if changing the year makes an invalid date?  Feb 29 only exists in leap years.

[Ihsan-Mahdi]

Thank you for your interest in my post! In my data, "Leave" comes first (records are EMS transports from scene to a hospital). Choosing which variable to change is based on the DAY, MONTH, and TIME parts (the "Leave" field should be earlier then 'Arrived").

The logic if deciding a different year is a problem is that a transport from scene to hospital should not logically be that long. I have identified a specific number of records where the YEAR difference is a problem, none of those records had dates between Dec. and Jan. or contained Feb. 29.

I wanted to use the code for changing the specific records identified and not the entire data set.

Thanks again 🙂