How to update data values based on the data

Reply
Occasional Contributor
Posts: 8

How to update data values based on the data

Hi all,

Consider the following partial data, dat1:

_n_timestateevent
1.94DC1
2.94PC0
3.94CC0
4.94DP0
5.94UU0
64.85PC1
74.85CC0
84.85DP0
94.85DC0
104.85UU0
118.53DP1
128.53PC0
138.53CC0
148.53DC0
158.53UU0
............

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. 

Super Contributor
Posts: 1,636

Re: How to update data values based on the data

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

Respected Advisor
Posts: 3,124

Re: How to update data values based on the data

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;

Respected Advisor
Posts: 3,887

Re: How to update data values based on the data

What about:

data want (drop=_Smiley Happy;

   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;

Respected Advisor
Posts: 3,124

Re: How to update data values based on the data

Hi Patrick,

Slick reversed approach!

One thing though, why not comment out

/*  else event='0';*/

so that other events stay unchanged?

Haikuo

Respected Advisor
Posts: 3,887

Re: How to update data values based on the data

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."

Ask a Question
Discussion stats
  • 5 replies
  • 269 views
  • 0 likes
  • 4 in conversation