topic Re: How to calculate Time difference in minutes? in SAS Programming

How to calculate Time difference in minutes?

zsame — Wed, 05 Feb 2020 13:04:51 GMT

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

Re: How to calculate Time difference in minutes?

yabwon — Wed, 05 Feb 2020 13:19:14 GMT

Hi,

counting minutes - I would try INTCK():

data _null_;
  a=time();
  b=a+360;

  c = INTCK("minutes",a,b);
  put _all_;
run;

Re: How to calculate Time difference in minutes?

Kurt_Bremser — Wed, 05 Feb 2020 13:27:19 GMT

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.

Re: How to calculate Time difference in minutes?

Kurt_Bremser — Wed, 05 Feb 2020 13:32:51 GMT

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).

Re: How to calculate Time difference in minutes?

ed_sas_member — Wed, 05 Feb 2020 13:41:03 GMT

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.

Re: How to calculate Time difference in minutes?

zsame — Sun, 09 Feb 2020 08:57:57 GMT

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

Re: How to calculate Time difference in minutes?

Kurt_Bremser — Sun, 09 Feb 2020 10:21:42 GMT

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

Re: How to calculate Time difference in minutes?

Tom — Sun, 09 Feb 2020 17:36:54 GMT

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.