topic Re: Counter by ID with a second nonsorted variable in SAS Programming

Counter by ID with a second nonsorted variable

Jrstanek12 — Tue, 22 Feb 2022 21:00:30 GMT

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!

Re: Counter by ID with a second nonsorted variable

PaigeMiller — Tue, 22 Feb 2022 21:30:14 GMT

data want;
    set have;
    by id;
    if first.id then episode=1;
    if not first.id and month=1 then episode+1;
run;

Re: Counter by ID with a second nonsorted variable

Kurt_Bremser — Wed, 23 Feb 2022 09:10:07 GMT

Slightly different coding philosophy:

data want;
set have;
by id;
if first.id
then episode = 1;
else if month = 1 then episode + 1;
run;

Re: Counter by ID with a second nonsorted variable

Jrstanek12 — Wed, 23 Feb 2022 18:12:59 GMT

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.