topic Re: Adjusting column value for changing values of a dummy in SAS Programming

Adjusting column value for changing values of a dummy

finans_sas — Sat, 26 Aug 2017 16:07:31 GMT

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

Re: Adjusting column value for changing values of a dummy

art297 — Sat, 26 Aug 2017 16:40:36 GMT

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

Re: Adjusting column value for changing values of a dummy

Astounding — Sat, 26 Aug 2017 18:27:49 GMT

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;

Re: Adjusting column value for changing values of a dummy

finans_sas — Mon, 04 Sep 2017 16:55:49 GMT

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