How to update data values based on the data

[HanJoeKim]

Hi all,

Consider the following partial data, dat1:

_n_	time	state	event
1	.94	DC	1
2	.94	PC	0
3	.94	CC	0
4	.94	DP	0
5	.94	UU	0
6	4.85	PC	1
7	4.85	CC	0
8	4.85	DP	0
9	4.85	DC	0
10	4.85	UU	0
11	8.53	DP	1
12	8.53	PC	0
13	8.53	CC	0
14	8.53	DC	0
15	8.53	UU	0
...	...	...	...

There are 5 competiting states: DC, PC, CC, DP and UU. The event indicates which state is observed at that time.

I am interested in the DC state, and whenever the DC state occurs, I want to update the event as the next occuring state. I also want to recode all the other events as zero.

In this example, on row _n_=1 shows that the DC state has occured (event=1) and the next state that occurred was "PC" (_n_=6) .

Therefore, instead of "1" for the event on row _n_=1, I want to change the event as "PC".

So, whenever the state is "DC" and the event is "1", I want to sustitute the event value to the next occuring state that has an event of "1".

Otherwise, I want the events to be zero.

How can I do this?

Thanks,

Hanjoe. 

[Linlin]

data have;

input n time (state  event) (:$2.);

cards;

1 .94 DC 1

2 .94 PC 0

3 .94 CC 0

4 .94 DP 0

5 .94 UU 0

6 4.85 PC 1

7 4.85 CC 0

8 4.85 DP 0

9 4.85 DC 0

10 4.85 UU 0

11 8.53 DP 1

12 8.53 PC 0

13 8.53 CC 0

14 8.53 DC 0

15 8.53 UU 0

;

proc sql;

create table temp1 as

   select n,state,event

     from have

    where state='DC' or event="1"

     order by n desc;

   quit;

   data temp2;

     set temp1;

  event=ifc(state='DC' and event='1' and lag(state) ne 'DC',lag(state),event);

  proc sort  data=temp2;

   by n ;

  data want;

  merge have(in=a) temp2;

  by n ;

  if a;

  proc print;run;

Obs     n    time    state    event

                          1     1    0.94     DC       PC

                          2     2    0.94     PC       0

                          3     3    0.94     CC       0

                          4     4    0.94     DP       0

                          5     5    0.94     UU       0

                          6     6    4.85     PC       1

                          7     7    4.85     CC       0

                          8     8    4.85     DP       0

                          9     9    4.85     DC       0

                         10    10    4.85     UU       0

                         11    11    8.53     DP       1

                         12    12    8.53     PC       0

                         13    13    8.53     CC       0

                         14    14    8.53     DC       0

                         15    15    8.53     UU       0

[Haikuo]

Raw data was stolen from Linlin's post:

data have;

input n time (state  event) (:$2.);

cards;

1 .94 DC 1

2 .94 PC 0

3 .94 CC 0

4 .94 DP 0

5 .94 UU 0

6 4.85 PC 1

7 4.85 CC 0

8 4.85 DP 0

9 4.85 DC 0

10 4.85 UU 0

11 8.53 DP 1

12 8.53 PC 0

13 8.53 CC 0

14 8.53 DC 0

15 8.53 UU 0

;

/*Two step, easy to follow*/

data _0;

  set have (where=(event='1')) end=last;

  set have (where=(_e='1') rename=(state=_s event=_e) keep=state event firstobs=2) have (drop=_all_ obs=1);

  event=ifc(state='DC' and not last,_s, event);

  drop _:;

run;

data want;

  merge have  _0;

  by n;

run;

/*One step 2XDOW*/

data want;

length event $2.;

   do until (state ne 'DC' and event='1' or last);

       set have end=last;

    end;

    _s=state;

   do until (state ne 'DC' and event='1');

       set have;

       if state='DC' and event='1' then event=_s;

       output;

    end;

    drop _s;

run;

[Patrick]

What about:

data want (drop=_:);

   if 0 then set have;

   do i=nobs to 1 by -1;

      if _Revent=1 then _Rstate=state;

      set have nobs=nobs point=i;

      _Revent=event;

      if event=1 and state='DC' then event=_Rstate;

      else event=0;

      output;

   end;

   stop;

run;

[Haikuo]

Hi Patrick,

Slick reversed approach!

One thing though, why not comment out

/*	else event='0';*/

so that other events stay unchanged?

Haikuo

[Patrick]

That's how I understood the requirement in the OP

"So, whenever the state is "DC" and the event is "1", I want to sustitute the event value to the next occuring state that has an event of "1".

Otherwise, I want the events to be zero."