BookmarkSubscribeRSS Feed
HanJoeKim
Calcite | Level 5

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. 

5 REPLIES 5
Linlin
Lapis Lazuli | Level 10

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
Onyx | Level 15

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
Opal | Level 21

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
Onyx | Level 15

Hi Patrick,

Slick reversed approach!

One thing though, why not comment out

/*  else event='0';*/

so that other events stay unchanged?

Haikuo

Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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