DATA Step, Macro, Functions and more

Arrival and Departure Delay

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Arrival and Departure Delay

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.


Accepted Solutions
Solution
‎01-30-2018 03:09 AM
PROC Star
Posts: 1,209

Re: Arrival and Departure Delay

[ Edited ]

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


All Replies
Regular Contributor
Posts: 201

Re: Arrival and Departure Delay

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?
Occasional Contributor
Posts: 5

Re: Arrival and Departure Delay

Posted in reply to error_prone

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

PROC Star
Posts: 1,209

Re: Arrival and Departure Delay

What does your code look like now?

Occasional Contributor
Posts: 5

Re: Arrival and Departure Delay

Using this code to get the delay:-

 

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

PROC Star
Posts: 1,209

Re: Arrival and Departure Delay

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?

Occasional Contributor
Posts: 5

Re: Arrival and Departure Delay

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
 
Super User
Posts: 9,873

Re: Arrival and Departure Delay

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎01-30-2018 03:09 AM
PROC Star
Posts: 1,209

Re: Arrival and Departure Delay

[ Edited ]

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;
Occasional Contributor
Posts: 5

Re: Arrival and Departure Delay

Understood...Thanks Smiley Happy
Super User
Posts: 9,873

Re: Arrival and Departure Delay

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 109

Re: Arrival and Departure Delay

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.

Super User
Posts: 9,873

Re: Arrival and Departure Delay

Posted in reply to Satish_Parida

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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