Hello!
I have a dataset with a sorted ID variable and unsorted month variable that resets to 1 every so often.
ID | Month |
1001 | 1 |
1001 | 2 |
1001 | 3 |
1001 | 1 |
1001 | 2 |
1002 | 1 |
1002 | 2 |
1002 | 3 |
1002 | 4 |
1003 | 1 |
1003 | 1 |
1003 | 2 |
1003 | 3 |
1003 | 1 |
1003 | 2 |
1003 | 1 |
1003 | 2 |
1003 | 3 |
1003 | 4 |
1003 | 5 |
I want to include another variable (Episode) which acts as an indicator for the number of series of months resets back to 1 for each ID.
ID | Month | Episode |
1001 | 1 | 1 |
1001 | 2 | 1 |
1001 | 3 | 1 |
1001 | 1 | 2 |
1001 | 2 | 2 |
1002 | 1 | 1 |
1002 | 2 | 1 |
1002 | 3 | 1 |
1002 | 4 | 1 |
1003 | 1 | 1 |
1003 | 1 | 2 |
1003 | 2 | 2 |
1003 | 3 | 2 |
1003 | 1 | 3 |
1003 | 2 | 3 |
1003 | 1 | 4 |
1003 | 2 | 4 |
1003 | 3 | 4 |
1003 | 4 | 4 |
1003 | 5 | 4 |
I've found many examples of posts with a similar problem but none of them are successful in doing what I need. I've tried first.variable and do loops, among others. I know there has to be a simple solution.
Here are two things I've tried that get close:
data want;
set have;
retain episode;
by ID month notsorted;
if month=1 then episode+1;
run;
data want;
set have;
by ID;
retain episode;
if month=1 then episode+1
run;
Thank you!
data want;
set have;
by id;
if first.id then episode=1;
if not first.id and month=1 then episode+1;
run;
data want;
set have;
by id;
if first.id then episode=1;
if not first.id and month=1 then episode+1;
run;
Thank you! This works perfectly. This is in the realm of what I was trying but still wasn't working, the first.id wasn't working properly. The episode counter wasn't resetting back to 1 for every subsequent ID and was just counting increasingly. My issue was I had other things going on in the data step prior to the by statement that were messing with the "by variable" logic so once I took that out it worked perfectly.
Slightly different coding philosophy:
data want;
set have;
by id;
if first.id
then episode = 1;
else if month = 1 then episode + 1;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.