Solved
New Contributor
Posts: 2

# Row Iterative Do-Loops and computed columns using stored values

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:

IDID_Aftertime_diffDuration
1110
1111
11181
12202
2211

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:

IDID_Aftertime_diffDurationSession
11101
11111
111811
122022
22113

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

Accepted Solutions
Solution
‎05-05-2014 10:43 PM
Posts: 5,523

## Re: Row Iterative Do-Loops and computed columns using stored values

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

PG

All Replies
Solution
‎05-05-2014 10:43 PM
Posts: 5,523

## Re: Row Iterative Do-Loops and computed columns using stored values

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

PG
New Contributor
Posts: 2

## Re: Row Iterative Do-Loops and computed columns using stored values

Hi PGStats;

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

Thanks,

Tyler

🔒 This topic is solved and locked.