BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
guimeleiro
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

7 REPLIES 7
ballardw
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5
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
Obsidian | Level 7

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

 

Capture.PNG

 

 

 

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

 

 

 

 

mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
mkeintz
PROC Star

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;

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 6092 views
  • 1 like
  • 4 in conversation