DATA Step, Macro, Functions and more

Adjusting column value for changing values of a dummy

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

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: 7,491

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

View solution in original post


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

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: 5,516

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
  • 115 views
  • 2 likes
  • 3 in conversation