Row Iterative Do-Loops and computed columns using stored values

tdiestel — Tue, 06 May 2014 00:06:42 GMT

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

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,

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

PGStats — Tue, 06 May 2014 02:43:04 GMT

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;

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

tdiestel — Tue, 06 May 2014 15:31:29 GMT

Hi PGStats;

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

Thanks,

Tyler

topic Re: Row Iterative Do-Loops and computed columns using stored values in SAS Programming

Row Iterative Do-Loops and computed columns using stored values

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

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