topic Re: How to calculate difference time (minutes) between two rows in SAS Programming

How to calculate difference time (minutes) between two rows

guimeleiro — Fri, 17 Nov 2017 19:33:12 GMT

I'm a layman in SAS. I'm using SAS Enterprise Guide and I have a problem.

I need to calculate the difference time (minutes) between two rows.

However, only calculate the difference when it's on the same day.

For example:

How to calculate difference time and to create the Difference Column?

Re: How to calculate difference time (minutes) between two rows

ballardw — Fri, 17 Nov 2017 20:01:15 GMT

Is your hour variable an actual SAS date value?

And do you want the result to be a count of minutes? Your result shows a time value but I am going to guess that's because you're working in Excel.

Re: How to calculate difference time (minutes) between two rows

guimeleiro — Fri, 17 Nov 2017 20:42:28 GMT

I imported the Excel data.
Actually "day" and "hour" are SAS date value.

Yes, I want the result to be a count of minutes.

Re: How to calculate difference time (minutes) between two rows

ballardw — Fri, 17 Nov 2017 22:14:28 GMT

This will give you the number of minutes with a caveat.

If your hour variable actually have second components you may not get what you want because the intck function counts interval boundaries. The second code example shows the time values 2 seconds apart but the function returns 1 "minute" interval.

If you need 60-second minutes then you'll get into some different rounding such as round( (hour-lhour)/60, 1) or possibly Floor or Ceil instead of Round depending on your need.

data want;
   set have;
   by day;
   lhour=lag(hour);
   if first.day then difference=.;
   else difference = intck('minute',lhour,hour);
   drop lhour;
run;

data junk;
   x= "12:10:59"t;
   y= "12:11:01"t;
   interval = intck('minute',x,y);
run;

Re: How to calculate difference time (minutes) between two rows

mkeintz — Fri, 17 Nov 2017 22:15:16 GMT

This is a good situation for the DIF function embedded in an IFN function:

data want;

set have;

by day;

difference=ifn(first.day,.,dif(hour));

format difference time8.0;

run;

Re: How to calculate difference time (minutes) between two rows

guimeleiro — Tue, 21 Nov 2017 20:45:04 GMT

Other doubt:
In case I want to calculate difference time (minutes) between two rows by conditioning it for day and group (Group A or Group B, for example), I put:

if first.day and first.group then difference=.;

Or Is there another way?

Re: How to calculate difference time (minutes) between two rows

MB_Analyst — Fri, 17 Aug 2018 14:52:00 GMT

How could I do the same process but have the last observation per group be the missing value. Such that:

EDIT: It looks as if I could use this methodology - http://www.sascommunity.org/wiki/Look-Ahead_and_Look-Back

Re: How to calculate difference time (minutes) between two rows

mkeintz — Sat, 18 Aug 2018 00:23:28 GMT

Yes, you can use that methodology:

data want (drop=next_hour);
  set have end=end_of_have;
  by day;
  set have (firstobs=2 keep=hour rename=(hour=next_hour));
  if last.day=0 then time_to_next=next_hour-hour;
run;