topic How to calculate the difference between two timestamps of different obs in SAS Programming

How to calculate the difference between two timestamps of different obs

PhilfromGermany — Tue, 01 Oct 2013 15:26:01 GMT

Hello, I have the following data set:

ID	Status	Timestamp
1122	idle	10MAY2013
1122	active	15MAY2013
1122	idle	25MAY2013
1122	closed	01JUN2013

Every time the status of an ID changes, a new record for that ID is being created and a timestamp of when the change occured is added to the record. That is how old statuses get historicized, as can be seen in the table.

I would like to add a column that measures how long a certain status has been assigned to an ID. The outcome would look like this:

ID	Status	Timestamp	Duration in days
1122	idle	10MAY2013	5
1122	active	15MAY2013	10
1122	idle	25MAY2013	7
1122	closed	01JUN2013	...

The last value (the cell that contains "...") would be created through additional logic, like

if last.id then duration=today()-timestamp

or something like that.

Your help is much appreciated!

Re: How to calculate the difference between two timestamps of different obs

PGStats — Tue, 01 Oct 2013 17:35:02 GMT

You can do it like this:

data want;

set have; by id;

lastId = last.id;

if not endNext then

set have(firstobs=2 keep=timeStamp rename=(timeStamp=nextTimeStamp))

end=endNext;

if lastId

then duration= intck("DAY", timeStamp, today());

else duration = intck('DAY', timeStamp, nextTimeStamp);

drop lastId nextTimeStamp;

run;

Message was edited by: PG : Added END= condition to prevent premature datastep ending.

Re: How to calculate the difference between two timestamps of different obs

Haikuo — Tue, 01 Oct 2013 19:21:58 GMT

A proc sql way:

proc sql;

select a.*, b.timestamp-a.timestamp as duration

from have a

left join have b

on a.id=b.id

and a.timestamp < b.timestamp

group by a.id, a.status, a.timestamp

having b.timestamp=min(b.timestamp)

order by id,timestamp;

quit;

Haikuo

Re: How to calculate the difference between two timestamps of different obs

Vince28_Statcan — Wed, 02 Oct 2013 12:21:50 GMT

If your dataset is already sorted by ID TIMESTAMP, then you can use the following:

data want;

set have;

by id;

if not last.id then do;

i=_N_+1;

set have(rename=(timestamp=nextstamp) keep=nextstamp) point=i;

duration=intck('day', timestamp, nextstamp);

end;

*drop nextstamp i;

run;

you may have to change the keep= set option to keep=timestamp. I always forget if rename applies before or after keep for set options. This effectively reads in the next TIMESTAMP into a new variable NEXTSTAMP using direct addressing of _N_+1 since your data is presorted.

edit the logic is very similar to that of PG

*edit corrected after PGs' comment. That is the kind of syntax restrictions that I always figure out debugging after my logic is established!

Vince

Re: How to calculate the difference between two timestamps of different obs

PGStats — Wed, 02 Oct 2013 19:04:09 GMT

, that's a clever use of random access. However, expressions are not allowed as values to the POINT= option. You must use an intermediate variable to hold the expression.

Re: How to calculate the difference between two timestamps of different obs

FredrikE — Thu, 03 Oct 2013 06:35:05 GMT

This involves double sorting, but if your data is not too big it might work....:)

data test;
length id 8 status $10 timestamp 8;
input id status timestamp anydtdte.;
format timestamp yymmdd10.;
datalines;
1122 idle 10may2013
1122 active 15may2013
1122 idle 25may2013
1122 closed 01jun2013
;
run;

proc sort data=test;
by id descending timestamp;
run;

data new;
set test;
retain tmp;
format nextts yymmdd10.;

if _n_ = 1 then tmp = .;
nextts = tmp;

if timestamp ne . then tmp = timestamp;

duration=intck('day', timestamp, nextts);
run;

proc sort data=new;
by id timestamp;
run;