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.
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;
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
What does your code look like now?
Using this code to get the delay:-
dep_delay = intck('minute',scd_dep_time,dept_time);
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?
I do have the date variable...Data shared below:-
Obs Date Scd_dep arr_time delay
6210 | 12/03/2013 | 8:35:00 PM | 8:53:00 PM | 18 |
6211 | 11/11/2013 | 2:59:00 PM | 2:56:00 PM | -3 |
6212 | 12/19/2013 | 8:27:00 PM | 8:22:00 PM | -5 |
6213 | 11/12/2013 | 8:30:00 AM | 10:06:00 AM | 96 |
6214 | 12/21/2013 | 2:59:00 PM | 3:27:00 PM | 28 |
6215 | 12/10/2013 | 8:35:00 PM | 8:31:00 PM | -4 |
6216 | 02/15/2013 | 3:05:00 PM | 3:03:00 PM | -2 |
6217 | 12/05/2013 | 8:35:00 PM | 12:27:00 AM | -1208 |
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.
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;
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.
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.
@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.
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.