topic Re: How to update data values based on the data in SAS Programming

How to update data values based on the data

HanJoeKim — Sat, 06 Oct 2012 01:29:45 GMT

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.

Re: How to update data values based on the data

Linlin — Sat, 06 Oct 2012 02:08:15 GMT

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

Re: How to update data values based on the data

Haikuo — Sat, 06 Oct 2012 03:53:15 GMT

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;

Re: How to update data values based on the data

Patrick — Sat, 06 Oct 2012 07:31:14 GMT

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;

Re: How to update data values based on the data

Haikuo — Sun, 07 Oct 2012 15:03:55 GMT

Hi Patrick,

Slick reversed approach!

One thing though, why not comment out

/*	else event='0';*/

so that other events stay unchanged?

Haikuo

Re: How to update data values based on the data

Patrick — Mon, 08 Oct 2012 14:08:38 GMT

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