Solved
Contributor
Posts: 57

Adjusting column value for changing values of a dummy

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

Accepted Solutions
Solution
‎09-04-2017 12:54 PM
PROC Star
Posts: 8,149

Re: Adjusting column value for changing values of a dummy

Posted in reply to finans_sas

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

All Replies
Solution
‎09-04-2017 12:54 PM
PROC Star
Posts: 8,149

Re: Adjusting column value for changing values of a dummy

Posted in reply to finans_sas

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

Super User
Posts: 6,637

Re: Adjusting column value for changing values of a dummy

Posted in reply to finans_sas

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;

Contributor
Posts: 57

Re: Adjusting column value for changing values of a dummy

Posted in reply to finans_sas

Thank you so much for your help! I love this forum.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 3 replies
• 143 views
• 2 likes
• 3 in conversation