How to find time difference b.w two dates on different observations

Reply
Frequent Contributor
Posts: 134

How to find time difference b.w two dates on different observations

Hi,

I have a data set with event details as follows

Event Name       Location               Start      End

ABC                        Loc A                     01Jan2012:10:0:0              01Jan2012:12:0:0

DEF                        Loc B                      02Jan2012:08:0:0              02Jan2012:11:0:0

GHI                        Loc B                      02Jan2012:18:0:0              02Jan2012:20:0:0

I want to know the time difference b.w end of the event and the start of the next event… something like this

Event Name       Location               Start                                      End                                        Next_Event_In_Hrs

ABC                        Loc A                     01Jan2012:10:0:0              01Jan2012:12:0:0                   20

DEF                        Loc B                      02Jan2012:08:0:0              02Jan2012:11:0:0                   7

GHI                        Loc B                      02Jan2012:18:0:0              02Jan2012:20:0:0                     -

Thanks in advance

PROC Star
Posts: 7,492

Re: How to find time difference b.w two dates on different observations

You could use something like:

data want (drop=next);

  set have;

  set have (firstobs=2 drop=Event_Name Location End rename=start=next)

      have (obs=1 drop=_all_);

  time_between=(next-start)/(60*60);

run;

Super User
Posts: 5,518

Re: How to find time difference b.w two dates on different observations

Assuming your START and END values are SAS datetime values, not character strings:

data want;

   set have end=last_one;

   if not last_one then do;

      set have (firstobs=2 keep=start rename=(start=next_starting_time));
      next_event_in_hrs = (next_starting_time - end) / 3600;

   end;

   drop next_starting_time;

run;

Good luck.

Respected Advisor
Posts: 3,156

Re: How to find time difference b.w two dates on different observations

Here is some undocumented SQL,

data have;

input Event_Name$       Location:$&8.               (Start      End) (:datetime30.);

format start end DATETIME30.;

cards;

ABC                        Loc A                     01Jan2012:10:0:0              01Jan2012:12:0:0

DEF                        Loc B                      02Jan2012:08:0:0              02Jan2012:11:0:0

GHI                        Loc B                      02Jan2012:18:0:0              02Jan2012:20:0:0

;

proc sql;

select a.*, intck('hour', a.end,b.start) as Next_Event_In_Hrs

   from (select *, monotonic() as _ano from have) a

   left join

   (select *, monotonic() as _bno from have) b

            on a._ano+1=b._bno;

            

quit;

Haikuo

Ask a Question
Discussion stats
  • 3 replies
  • 205 views
  • 0 likes
  • 4 in conversation