CODEWITHSUNDEEP
timesas
Taylrl
Taylrl

Reputation: 3919

Calculating an interval from a timestamp in SAS

I have some data:

data beforehave;
   input ID TIME_EVENT $ Activity $;
   datalines;
12345 07:03:875 Activity1
12345 07:04:004 Activity1
12345 07:05:062 Activity1 
12345 07:07:357 Activity2 
12345 07:10:743 Activity2 
23145 07:12:737 Activity1 
23145 07:14:065 Activity2 
23145 07:15:037 Activity2 
;
RUN;

I want to get to some data that looks like this where I am counting the time between steps but then resetting the counter to 0 every time the first Activity 1 appears;

data beforehave;
   input ID TIME_EVENT $ Activity $ TIME_TAKEN;
   datalines;
12345 07:03:875 Activity1 00:00:000
12345 07:04:004 Activity1 00:00:029
12345 07:05:062 Activity1 00:01:058
12345 07:07:357 Activity2 00:01:295
12345 07:10:743 Activity2 00:03:386
23145 07:12:737 Activity1 00:00:000
23145 07:14:065 Activity2 00:01:672
23145 07:15:037 Activity2 00:00:972
;
RUN;

I think I need to take the time the particular Activity occurred from the time the first Activity1 occurred for that ID. I have thought of doing this in terms of an intermediate step whereby I create a field that updates to pull across the TIME_EVENT of the fist Activity1. This intermediate step would look like this;

data beforehave;
   input ID TIME_EVENT $ Activity $ TIME_INTER;
   datalines;
12345 07:03:875 Activity1 07:03:875
12345 07:04:004 Activity1 07:03:875
12345 07:05:062 Activity1 07:03:875
12345 07:07:357 Activity2 07:03:875
12345 07:10:743 Activity2 07:03:875
23145 07:12:737 Activity1 07:12:737
23145 07:14:065 Activity2 07:12:737
23145 07:15:037 Activity2 07:12:737
;
RUN;

To create this I could use the SAS first. function and have written this;

data OUT.DATA;    
   set IN.DATA;
   if first.Activity = 'Activity1' then TIME_INTER = TIME_EVENT;
run;

from this I just get a blank field.

Could anyone help?

Upvotes: 2

Views: 103

Answers (2)

Taylrl
Taylrl

Reputation: 3919

It turns out my TIME_EVENT field was actually a character format, so that had to be recast as a time data-type. Also I only had a time stamp for the beginning of the last type of Activity so the TIME_INTERVAL became the time before the next Activity1 which was effectively meaningless. I therefore had to set this to 0. This code is basically the same as the one posted above by andrey_sz only with a couple of tweeks for those things mentioned above;

data OUT.DATA(drop=TIME_EVENT_prev);
    set beforehave;
    by ID;
    TIME_EVENT=input(TIME_EVENT,TIME8.);
    retain TIME_EVENT_prev;
    if first.ID then TIME_INTER = 0;
    else TIME_INTER = TIME_EVENT +(-TIME_EVENT_prev);
    TIME_EVENT_prev = TIME_EVENT;
    if last.logflag then TIME_INTER = 0;
run;

Upvotes: 1

andrey_sz
andrey_sz

Reputation: 751

data OUT.DATA(drop=TIME_EVENT_prev);
   set beforehave;
   by ID;
   retain TIME_EVENT_prev;
   if first.ID then TIME_INTER = 0;
   else TIME_INTER = TIME_EVENT +(-TIME_EVENT_prev);
   TIME_EVENT_prev = TIME_EVENT;
run;

Retain variable TIME_EVENT_prev is fixed on each row and before fixing on current row it has value from previous rows.

Upvotes: 1

Related Questions