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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.