Row Iterative Do-Loops and computed columns using stored values

[tdiestel]

Hi All;

I have a situations which seems easy to accomplish, yet after many attempts am still unsuccessful. I have one dataset of events with these variables: id (identifier for a subject), id_after (id for the row following), time_diff (time between the current row and the row after),  and duration (the time the subject spent for the current row).

Current:

ID	ID_After	time_diff	Duration
1	1	1	0
1	1	1	1
1	1	18	1
1	2	20	2
2	2	1	1

What I would like to do is create a new variable called 'session', which will mark each row as being part of a particular session with some specified logic I put in place. Thus this new column would act like a session ID to which I can then do calculation like 'how many sessions does a user have?' or 'how long is the average session?'

The resulting table would look like this where there are 3 unique sessions:

ID	ID_After	time_diff	Duration	Session
1	1	1	0	1
1	1	1	1	1
1	1	18	1	1
1	2	20	2	2
2	2	1	1	3

Here's my logic:

First declare a variable sessionID and set it to 1 and then in the do-loop do this:

session=&sessionID.;

if id = id_after

and time_diff <2

or

id = id_after

and time_dif <= duration

then %let sessionID=%eval(&sessionID.);

else %let sessionID=%eval(&sessionID.+1);

Any ideas how I can get to the final goal of this loop with this logic? I have looked at SQL cursors and some iterative Macros with do loops but all to no avail. Any help is greatly appreciative.

Thanks,

T

[PGStats · Accepted solution]

No need for macro programming here.

data have;

input ID ID_After time_diff Duration;

datalines;

1 1 1 0

1 1 1 1

1 1 18 1

1 2 20 2

2 2 1 1

;

data want;

set have; by ID;

session + first.id or (lag(time_diff) > max(1, lag(duration)));

run;

proc print data=want noobs; run;

PG

[tdiestel]

Hi PGStats;

This is great!!! You have saved me so much time, and I really appreciate your help.

Thanks,

Tyler