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?
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;
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.
I imported the Excel data.
Actually "day" and "hour" are SAS date value.
Yes, I want the result to be a count of minutes.
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;
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
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.