## How to update data values based on the data

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

Posts: 3,167

## 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;

Posts: 4,736

## Re: How to update data values based on the data

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;

Posts: 3,167

## 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

Posts: 4,736

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

Discussion stats
• 5 replies
• 292 views
• 0 likes
• 4 in conversation