How to calculate difference time (minutes) between two rows

[guimeleiro]

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?

[ballardw]

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.

[guimeleiro]

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

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

[ballardw · Accepted solution]

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;

[guimeleiro]

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?

[MB_Analyst]

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

[mkeintz]

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;

[mkeintz]

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;