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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.