BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
abhi18
Calcite | Level 5

Hi, So I have a dataset of Flights. In that i have Variables like Schedule_dep, Schedule_arr, actual_dep, actual_arr and I have to calculate delay from these variables.

 

The problem here is whenever the date changes for eg:-

Schedule_dep:- 11:45 Pm

Actual_dep:- 01:00 Am

 

The result that I'm getting is something -2315 instead of 75 minutes.

 

How to resolve this Plz help.

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20

If you have a date and a time for both the acheduled and the actual departure time, then convert to datetime values and use the INTCK function on those

 

data have;
input Schedule_dep_date:date9. Schedule_dep_time:time. Actual_dep_date:date9. Actual_dep_time:time.;
format Schedule_dep_date Actual_dep_date date9. Schedule_dep_time Actual_dep_time time.;
datalines;
30jan2018 11:45PM 31jan2018 01:00AM
;

data want;
   set have;
   Schedule_dep_dt=dhms(Schedule_dep_date,0,0,Schedule_dep_time);
   Actual_dep_dt=dhms(Actual_dep_date,0,0,Actual_dep_time);
   dep_delay=intck('minute',Schedule_dep_dt, Actual_dep_dt);
   format dt datetime.;
run;

View solution in original post

12 REPLIES 12
error_prone
Barite | Level 11
Please post example data. It is not clear of what type the variables are. How do you calculate the difference? Have you tried the appropriate function?
abhi18
Calcite | Level 5

Data types are:-

Schedule and Actual arrival :- Numeric 

The function that I'm using is Intck(), So I'm getting the desired output for rest of the observations only those are creating a problem in which the date for actual_dep changes after 00:00 HRS

PeterClemmensen
Tourmaline | Level 20

What does your code look like now?

abhi18
Calcite | Level 5

Using this code to get the delay:-

 

dep_delay = intck('minute',scd_dep_time,dept_time);

PeterClemmensen
Tourmaline | Level 20

If you do not bring the dates into the calculations, you will not be able to get the desired result. What does your data look like? Do you have a date variable as well?

abhi18
Calcite | Level 5

I do have the date variable...Data shared below:-

 

Obs           Date            Scd_dep       arr_time         delay

621012/03/20138:35:00 PM8:53:00 PM18 
621111/11/20132:59:00 PM2:56:00 PM-3 
621212/19/20138:27:00 PM8:22:00 PM-5 
621311/12/20138:30:00 AM10:06:00 AM96 
621412/21/20132:59:00 PM3:27:00 PM28 
621512/10/20138:35:00 PM8:31:00 PM-4 
621602/15/20133:05:00 PM3:03:00 PM-2 
621712/05/20138:35:00 PM12:27:00 AM-1208
 
Kurt_Bremser
Super User

To get precise results, you need to have dates for scheduled and actual time. Otherwise, you'll have to make guesses (if the number is far enough in the negative range, add 1440 (number of minutes for a day)). But that will obviously fail when the delay exceeds one day.

PeterClemmensen
Tourmaline | Level 20

If you have a date and a time for both the acheduled and the actual departure time, then convert to datetime values and use the INTCK function on those

 

data have;
input Schedule_dep_date:date9. Schedule_dep_time:time. Actual_dep_date:date9. Actual_dep_time:time.;
format Schedule_dep_date Actual_dep_date date9. Schedule_dep_time Actual_dep_time time.;
datalines;
30jan2018 11:45PM 31jan2018 01:00AM
;

data want;
   set have;
   Schedule_dep_dt=dhms(Schedule_dep_date,0,0,Schedule_dep_time);
   Actual_dep_dt=dhms(Actual_dep_date,0,0,Actual_dep_time);
   dep_delay=intck('minute',Schedule_dep_dt, Actual_dep_dt);
   format dt datetime.;
run;
abhi18
Calcite | Level 5
Understood...Thanks 🙂
Kurt_Bremser
Super User

Your problem comes from the fact that you are working with time values, while you should be working with datetime values.

I take it that your dataset also contains dates; you can easily create datetimes fro the date and time values.

 

Please show the code you are using now, and a quick overview of the variables in your dataset, with type and formats.

 

Satish_Parida
Lapis Lazuli | Level 10

You need to have date with time, to get the exact result.

If you have the date of departure with you, then multiply it by 3600 and then add it to time of departure. Same with arrival.

Then you do not need to use any function just subtraction will give you the difference of time in seconds.
Afterwards you can use formats to display them as you want.

Kurt_Bremser
Super User

@Satish_Parida wrote:

You need to have date with time, to get the exact result.

If you have the date of departure with you, then multiply it by 3600 and then add it to time of departure. Same with arrival.

Then you do not need to use any function just subtraction will give you the difference of time in seconds.
Afterwards you can use formats to display them as you want.


Correction: a day has 86400 seconds, so the date needs to be multiplied by that.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1473 views
  • 0 likes
  • 5 in conversation