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.
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
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;
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;
Hi Patrick,
Slick reversed approach!
One thing though, why not comment out
/* | else event='0';*/ |
so that other events stay unchanged?
Haikuo
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."
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.