CODEWITHSUNDEEP
timetimestampspssdifference
Duncan Talbot
Duncan Talbot

Reputation: 61

How to work out time difference from first timestamp for each ID SPSS

I would like to calculate the time difference in months from the first timestamp for each row of the same ID.

For example my data currently looks similar to this:

ID TimeStamp 

1 01-Jan-21

1 26-Apr-21

2 03-Jan-21

2 26-May-21

2 26-Oct-21

3 04-Jan-21

3 18-Mar-21

I would like it to look like this:

ID TimeStamp MonthSince1st

1 01-Jan-21 .

1 26-Apr-21 3

2 03-Jan-21 .

2 26-May-21 4

2 26-Oct-21 9

3 04-Jan-21 .

3 18-Mar-21 2

What should I do?

Upvotes: 1

Views: 98

Answers (1)

eli-k
eli-k

Reputation: 11360

This code first makes sure that we're working with a date variable and not a string - if timestamp is already a date variable you can skip the first line and continue with timestamp instead of ts.

compute ts=number(timestamp, DATE9).
formats ts (DATE9).

The aggregate command will add the first timestamp to all the rows of the ID. Then we can calculate the difference between the timestamp in each row and the first one for that ID.

aggregate outfile=* mode=addvariables /break=id /startdate=min(ts).
compute MonthsFromStart=DATEDIF(ts, startdate, "months").

If instead of 0 in the first row of that ID you prefer a missing value, use this instead of the second line:

sort cases by id ts.
if id=lag(id) MonthsFromStart=DATEDIF(ts, startdate, "months").

Upvotes: 1

Related Questions