DATA Step, Macro, Functions and more

How to calculate difference time (minutes) between two rows

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

How to calculate difference time (minutes) between two rows

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:

 swws.JPG

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


Accepted Solutions
Solution
‎11-21-2017 02:49 PM
Super User
Posts: 13,008

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

[ Edited ]
Posted in reply to guimeleiro

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;

 

View solution in original post


All Replies
Super User
Posts: 13,008

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

Posted in reply to guimeleiro

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.

New Contributor
Posts: 4

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

[ Edited ]

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

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

Solution
‎11-21-2017 02:49 PM
Super User
Posts: 13,008

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

[ Edited ]
Posted in reply to guimeleiro

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;

 

New Contributor
Posts: 4

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

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?
Trusted Advisor
Posts: 1,284

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

Posted in reply to guimeleiro

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 303 views
  • 1 like
  • 3 in conversation