Hi Everyone:
I posted below an example data set along with a column that I would like to create. In this data set, I have different people (personid) and for some observations, an event is present (1) and for some it is not (0). The data are sorted by personid and date, which I did not show. I want to create a new column (want):
-whenever, event=0, it should be 0.
-for a given personid: for the first event=1, it should be 1, and should stay as 1 as long as event=1.
-for a given personid: each time, the event transitions from 0 to 1, its value increases by 1.
I do not know how exactly I should go about this. I would very much appreciate your help.
Thank you
personid event want
1 0 0
1 0 0
1 0 0
1 0 0
1 0 0
1 1 1
1 1 1
1 1 1
1 1 1
1 1 1
1 0 0
1 0 0
1 1 2
1 1 2
1 1 2
1 0 0
1 1 3
2 1 1
2 1 1
2 0 0
2 0 0
2 0 0
2 0 0
2 1 2
2 1 2
2 1 2
2 0 0
2 0 0
2 1 3
2 1 3
2 1 3
2 0 0
2 1 4
The following will do what you want:
data want (drop=_:); set have; by personid; _last_event=ifn(not first.personid,lag(event),0); if first.personid then _hold=0; if event eq 1 and _last_event eq 0 then _hold+1; want=ifn(event eq 0,0,_hold); run;
Art, CEO, AnalystFinder.com
The following will do what you want:
data want (drop=_:); set have; by personid; _last_event=ifn(not first.personid,lag(event),0); if first.personid then _hold=0; if event eq 1 and _last_event eq 0 then _hold+1; want=ifn(event eq 0,0,_hold); run;
Art, CEO, AnalystFinder.com
One possibility:
data want;
set have;
by personid event notsorted;
if first.personid then event_counter=0;
if first.event and event=1 then event_counter + 1;
if event=0 then want=0;
else want = event_counter;
drop event_counter;
run;
Thank you so much for your help! I love this forum.
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.