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
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
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
Hi PGStats;
This is great!!! You have saved me so much time, and I really appreciate your help.
Thanks,
Tyler
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.