I am facing issues with calculation of the time diffference for time expressed in hhmm,
the data is representwed in decimals and would like to see the data represented in time format.
i tried the code below :
data eail ;
infile cards dlm='09'x truncover ;
input NO 1-2 Code $ Stn_Name: $25.;
input Zone $ Div $ Arr $ Dep $ Halt PF Dist Day $ Remark $;
ARRRIVAL=input(cats(Arr,"00"),hhmm5.);
*DEPARTURE=input(Dep, ?? hhmmss5.);
DEPARTURE=input(cats(Dep,"00"),hhmm5.);
lag_N_dep = lag(DEPARTURE);
DURv1= (DEPARTURE-lag_N_dep); /* Tried Different versions to get time difference */
DURv2= intck('Minute',DEPARTURE,lag_N_dep); /* Tried Different versions to get time difference */
DURRdiff_in_hrMin= (DEPARTURE-lag_N_dep)/.60; /* Tried version to get time difference in hours and minutes */
*format lag_N_dep DEPARTURE ARRRIVAL hhmmss5.;
DDIST= (pf- lag(pf));/* tried to get difference from above row to below */
drop Zone Div PF Day Remark halt dist;
Cards;
1 XUR Xolapier
CR XUR First 06.25 0 0 1
2 HG Ghofti
CR XUR 06.43 06.45 2 15 1
3 IDR Indixl
ALE BMX 07.24 07.25 1 58 1
4 BJP Viyaura
ALE BMX 08.15 08.20 5 109 1
5 BXRX BaBagead
ALE BMX 09.08 09.10 2 153 1
6 LMT Almanxti
ALE BMX 09.29 09.30 1 171 1
7 BGK Bavapgok
ALE BMX 10.18 10.20 2 206 1
8 GED Guldegua
ALE BMX 10.24 10.25 1 219 1
9 BDM Bamani
ALE BMX 10.39 10.40 1 232 1
10 HLAR Hilre Aut
ALE BMX 11.09 11.10 1 251 1
11 MLP Maltar
ALE BMX 11.29 11.30 1 268 1
12 GDG Gagduma
ALE BMX 12.23 12.25 2 299 1
13 NGR Anniton
ALE BMX 13.14 13.15 1 321 1
14 BMX Hujatig
ALE BMX 14.25 Last 0 357 1
;
run;
proc print data=eail ;
run;
Facing a couple of issues :
1. while reading data the column PF is empty (Blank) but dist and day is populated , hence the data for dist is read into PF column and data for Day into Dist.
Halt PF Dist Day $ Remark $;
0 0 1
Obs NO Code Stn_Name Arr Dep ARRRIVAL DEPARTURE lag_N_dep DURv1 DURv2 DURRdiff_in_hrMin DDIST12345
1 | XUR | Xolapier | First | 06.25 | . | 6.25 | . | . | . | . | . |
2 | HG | Ghofti | 06.43 | 06.45 | 6.43 | 6.45 | 6.25 | 0.20 | 0 | 0.33333 | 15 |
3 | IDR | Indixl | 07.24 | 07.25 | 7.24 | 7.25 | 6.45 | 0.80 | 0 | 1.33333 | 43 |
4 | BJP | Viyaura | 08.15 | 08.20 | 8.15 | 8.20 | 7.25 | 0.95 | 0 | 1.58333 | 51 |
5 | BXRX | BaBagead | 09.08 | 09.10 | 9.08 | 9.10 | 8.20 | 0.90 | 0 | 1.50000 | 44 |
the column Durr 1 has incorrect values as the third row should have 40 minutes instead of 80
3.Display of the final output showing time as 06:25 instead of 6.25 using format does not work.
thanks
Hi,
counting minutes - I would try INTCK():
data _null_;
a=time();
b=a+360;
c = INTCK("minutes",a,b);
put _all_;
run;
Your code does throw an ERROR because of an invalid informat (HHMM is not available as informat).
Please repost your code, using the "little running man" icon. The main posting window replaces tabs with blanks, so we can't copy/paste and run your code successfully anyway.
Or make changes in the code, so that blanks work, and proper formats are used:
data eail ;
infile cards truncover ;
input NO 1-2 Code $ Stn_Name $25.;
input Zone $ Div $ Arr $ Dep $ Halt PF Dist Day $ Remark $;
format arrival departure lag_N_dep time8.;
ARRIVAL=input(Arr,time5.);
DEPARTURE=input(Dep,time5.);
lag_N_dep = lag(DEPARTURE);
DURv1= (DEPARTURE-lag_N_dep); /* Tried Different versions to get time difference */
DURv2= intck('Minute',lag_N_dep,DEPARTURE); /* Tried Different versions to get time difference */
DURRdiff_in_hrMin= (DEPARTURE-lag_N_dep)/.60; /* Tried version to get time difference in hours and minutes */
DDIST= (pf- lag(pf));/* tried to get difference from above row to below */
drop Zone Div PF Day Remark halt dist;
Cards;
1 XUR Xolapier
CR XUR First 06.25 0 0 1
2 HG Ghofti
CR XUR 06.43 06.45 2 15 1
3 IDR Indixl
ALE BMX 07.24 07.25 1 58 1
4 BJP Viyaura
ALE BMX 08.15 08.20 5 109 1
5 BXRX BaBagead
ALE BMX 09.08 09.10 2 153 1
6 LMT Almanxti
ALE BMX 09.29 09.30 1 171 1
7 BGK Bavapgok
ALE BMX 10.18 10.20 2 206 1
8 GED Guldegua
ALE BMX 10.24 10.25 1 219 1
9 BDM Bamani
ALE BMX 10.39 10.40 1 232 1
10 HLAR Hilre Aut
ALE BMX 11.09 11.10 1 251 1
11 MLP Maltar
ALE BMX 11.29 11.30 1 268 1
12 GDG Gagduma
ALE BMX 12.23 12.25 2 299 1
13 NGR Anniton
ALE BMX 13.14 13.15 1 321 1
14 BMX Hujatig
ALE BMX 14.25 Last 0 357 1
;
proc print data=eail;
run;
You can see in the result that the difference between rows 2 and 3 are indeed 40 minutes.
To calculate a difference in minutes, use intck():
DURRdiff_in_Min = intck('minute',lag_N_dep,DEPARTURE);
but if you want it in HH:MM format, just apply hhmm5. to the difference in seconds, which you already have (durv1).
Hi @zsame
Does these statements answer your question?
format ARRIVAL DEPARTURE tod5.;
if Arr not in ("First", "Last") then ARRIVAL = input(Arr,time5.);
if Dep not in ("First", "Last") then DEPARTURE = input(Dep,time5.);
DUR = intck('minute',lag(DEPARTURE),DEPARTURE);
DDIST=(pf- lag(pf));
The format tod5. will add the leading zeros.
data eail;
infile cards dlm='09'x truncover;
input NO 1-2 Code $ Stn_Name: $25.;
input Zone $ Div $ Arr $ Dep $ Halt PF Dist Day $ Remark $;
/*ARRRIVAL=input(cats(Arr, "00"), hhmm5.);
*DEPARTURE=input(Dep, ?? hhmmss5.);
DEPARTURE=input(cats(Dep, "00"), hhmm5.);
lag_N_dep=lag(DEPARTURE);
DURv1=(DEPARTURE-lag_N_dep);
/* Tried Different versions to get time difference */
DURv2=intck('Minute', DEPARTURE, lag_N_dep);
/* Tried Different versions to get time difference */
DURRdiff_in_Min = intck('minute',lag_N_dep,DEPARTURE);
/* Tried version to get time difference in hours and minutes */
*format lag_N_dep DEPARTURE ARRRIVAL hhmmss5.;
DDIST=(pf- lag(pf));
*/
/* tried to get difference from above row to below */
drop Zone Div PF Day Remark halt dist;
Cards;
1 XUR Xolapier
CR XUR First 06.25 0 0 1
2 HG Ghofti
CR XUR 06.43 06.45 2 15 1
3 IDR Indixl
ALE BMX 07.24 07.25 1 58 1
4 BJP Viyaura
ALE BMX 08.15 08.20 5 109 1
5 BXRX BaBagead
ALE BMX 09.08 09.10 2 153 1
6 LMT Almanxti
ALE BMX 09.29 09.30 1 171 1
7 BGK Bavapgok
ALE BMX 10.18 10.20 2 206 1
8 GED Guldegua
ALE BMX 10.24 10.25 1 219 1
9 BDM Bamani
ALE BMX 10.39 10.40 1 232 1
10 HLAR Hilre Aut
ALE BMX 11.09 11.10 1 251 1
11 MLP Maltar
ALE BMX 11.29 11.30 1 268 1
12 GDG Gagduma
ALE BMX 12.23 12.25 2 299 1
13 NGR Anniton
ALE BMX 13.14 13.15 1 321 1
14 BMX Hujatig
ALE BMX 14.25 Last 0 357 1
;
run;
proc print data=eail;
run;
Hi,
Thank you all for all the replies , I tried most of the suggestions with little luck .
however i am posting the SAS code in a proper format .
Thanks
Why do you append "00" to a content that is already 5 characters long, when you then apply an informat with a length of 5? That's unnecessary and only confusing.
Maxim 2: READ THE LOG!!! It will alert you to your mistakes.
Corrected code:
data eail;
infile cards dlm='09'x truncover;
input NO Code :$3. Stn_Name $25.;
input Zone :$3. Div :$3. Arr :$5. Dep :$5. Halt PF Dist Day $ Remark $;
if arr ne 'First' then
ARRRIVAL = input(Arr,time5.);
If dep ne 'Last' then
DEPARTURE = input(Dep,time5.);
lag_N_dep=lag(DEPARTURE);
DURv1 = DEPARTURE - lag_N_dep;
DURv2 = intck('Minute', lag_N_dep, DEPARTURE);
DURRdiff_in_Min = intck('minute',lag_N_dep,DEPARTURE);
format lag_N_dep DEPARTURE ARRRIVAL hhmm5.;
DDIST=(pf- lag(pf));
drop Zone Div PF Day Remark halt dist;
Cards;
1 XUR Xolapier
CR XUR First 06.25 0 0 1
2 HG Ghofti
CR XUR 06.43 06.45 2 15 1
3 IDR Indixl
ALE BMX 07.24 07.25 1 58 1
4 BJP Viyaura
ALE BMX 08.15 08.20 5 109 1
5 BXRX BaBagead
ALE BMX 09.08 09.10 2 153 1
6 LMT Almanxti
ALE BMX 09.29 09.30 1 171 1
7 BGK Bavapgok
ALE BMX 10.18 10.20 2 206 1
8 GED Guldegua
ALE BMX 10.24 10.25 1 219 1
9 BDM Bamani
ALE BMX 10.39 10.40 1 232 1
10 HLAR Hilre Aut
ALE BMX 11.09 11.10 1 251 1
11 MLP Maltar
ALE BMX 11.29 11.30 1 268 1
12 GDG Gagduma
ALE BMX 12.23 12.25 2 299 1
13 NGR Anniton
ALE BMX 13.14 13.15 1 321 1
14 BMX Hujatig
ALE BMX 14.25 Last 0 357 1
;
proc print data=eail noobs;
run;
Result:
NO |
Code |
Stn_Name |
Arr |
Dep |
ARRRIVAL |
DEPARTURE |
lag_N_dep |
DURv1 |
DURv2 |
DURRdiff_in_Min |
DDIST |
1 |
XUR |
Xolapier |
First |
06.25 |
. |
6:25 |
. |
. |
. |
. |
. |
2 |
HG |
Ghofti |
06.43 |
06.45 |
6:43 |
6:45 |
6:25 |
1200 |
20 |
20 |
15 |
3 |
IDR |
Indixl |
07.24 |
07.25 |
7:24 |
7:25 |
6:45 |
2400 |
40 |
40 |
43 |
4 |
BJP |
Viyaura |
08.15 |
08.20 |
8:15 |
8:20 |
7:25 |
3300 |
55 |
55 |
51 |
5 |
BXR |
BaBagead |
09.08 |
09.10 |
9:08 |
9:10 |
8:20 |
3000 |
50 |
50 |
44 |
6 |
LMT |
Almanxti |
09.29 |
09.30 |
9:29 |
9:30 |
9:10 |
1200 |
20 |
20 |
18 |
7 |
BGK |
Bavapgok |
10.18 |
10.20 |
10:18 |
10:20 |
9:30 |
3000 |
50 |
50 |
35 |
8 |
GED |
Guldegua |
10.24 |
10.25 |
10:24 |
10:25 |
10:20 |
300 |
5 |
5 |
13 |
9 |
BDM |
Bamani |
10.39 |
10.40 |
10:39 |
10:40 |
10:25 |
900 |
15 |
15 |
13 |
10 |
HLA |
Hilre Aut |
11.09 |
11.10 |
11:09 |
11:10 |
10:40 |
1800 |
30 |
30 |
19 |
11 |
MLP |
Maltar |
11.29 |
11.30 |
11:29 |
11:30 |
11:10 |
1200 |
20 |
20 |
17 |
12 |
GDG |
Gagduma |
12.23 |
12.25 |
12:23 |
12:25 |
11:30 |
3300 |
55 |
55 |
31 |
13 |
NGR |
Anniton |
13.14 |
13.15 |
13:14 |
13:15 |
12:25 |
3000 |
50 |
50 |
22 |
14 |
BMX |
Hujatig |
14.25 |
Last |
14:25 |
. |
13:15 |
. |
. |
. |
36 |
while reading data the column PF is empty (Blank) but dist and day is populated , hence the data for dist is read into PF column and data for Day into Dist.
You are using list mode to read the data so you need to represent the missing values with a period.
If the data is actually in fixed columns (hard to tell from your post since you pasted the code/data directly into the mesage instead of using the Insert Code or Insert SAS Code icons) then read it using column based or formatted input style instead. Then the blank spaces will be treated as indicating a missing value instead of just padding.
Display of the final output showing time as 06:25 instead of 6.25 using format does not work.
Not sure if there is a SAS format to display hours and minutes separated by period instead of colon. You could build your own picture format using PROC FORMAT.
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.