assign value for a group under condition

[Zhongda]

Hi All,

I am new to sas and I was trying to achieve such thing:

here is the dataset:

PERMNO EVTDATE first_event inter_event

10001 8/1/11 1 0

10001 4/2/12 0 1

10002 7/6/98 1 0

10002 10/27/98 0 1

10011 11/13/95 1 0

10011 5/28/96 0 1

10011 5/29/96 0 1

10016 12/9/99 1 0

10016 5/3/00 0 1

10020 4/6/87 1 0

10020 10/21/87 0 1

10020 12/11/87 0 1

10020 4/17/90 1 0

10020 4/24/90 0 1

10028 8/17/99 1 0

10028 3/3/00 0 1

10028 7/14/06 1 0

10028 5/10/07 0 1

I would like to create another column call program and the column will state as:

PERMNO EVTDATE first_event inter_event define a program, and numbers of event in the group

10001 8/1/11 1 0 2

10001 4/2/12 0 1 2

10002 7/6/98 1 0 2

10002 10/27/98 0 1 2

10011 11/13/95 1 0 3

10011 5/28/96 0 1 3

10011 5/29/96 0 1 3

10016 12/9/99 1 0 2

10016 5/3/00 0 1 2

10020 4/6/87 1 0 3

10020 10/21/87 0 1 3

10020 12/11/87 0 1 3

10020 4/17/90 1 0 2

10020 4/24/90 0 1 2

10028 8/17/99 1 0 2

10028 3/3/00 0 1 2

10028 7/14/06 1 0 2

10028 5/10/07 0 1 2

let say for permno 10001, there are two obs and they form a program and the program has two event in a row, so it will be definded as 2 for each of the two obs.

Thank you so much for your help!

Appreciated,

Zhongda

[PeterClemmensen · Accepted solution]

Here is how I interpret your problem. You want to count the number of events (first_event or inter_event) that occurs within a 'group'. A group starts with a first_event and ends with the last inter_event = 1 or when Permno has a new value in the next obs.

This gives you your desired result. You would have to tweak it a bit if first_event and inter_event can both be =0 in the same obs.

data have;
input PERMNO EVTDATE :mmddyy8. first_event inter_event;
format EVTDATE mmddyy8.;
datalines;
10001 8/1/11   1 0
10001 4/2/12   0 1
10002 7/6/98   1 0
10002 10/27/98 0 1
10011 11/13/95 1 0
10011 5/28/96  0 1
10011 5/29/96  0 1
10016 12/9/99  1 0
10016 5/3/00   0 1
10020 4/6/87   1 0
10020 10/21/87 0 1
10020 12/11/87 0 1
10020 4/17/90  1 0
10020 4/24/90  0 1
10028 8/17/99  1 0
10028 3/3/00   0 1
10028 7/14/06  1 0
10028 5/10/07  0 1
;

data want;
   if 0 then set have;
   do _N_ = 1 by 1 until (last.inter_event & inter_event);
      set have;
      by permno inter_event notsorted;
      c + 1;
   end;

   do _N_ = 1 to _N_;
      set have;
      output;
   end;

   c = 0;
run;

Result:

PERMNO  EVTDATE   first_event  inter_event  c
10001   08/01/11  1            0            2
10001   04/02/12  0            1            2
10002   07/06/98  1            0            2
10002   10/27/98  0            1            2
10011   11/13/95  1            0            3
10011   05/28/96  0            1            3
10011   05/29/96  0            1            3
10016   12/09/99  1            0            2
10016   05/03/00  0            1            2
10020   04/06/87  1            0            3
10020   10/21/87  0            1            3
10020   12/11/87  0            1            3
10020   04/17/90  1            0            2
10020   04/24/90  0            1            2
10028   08/17/99  1            0            2
10028   03/03/00  0            1            2
10028   07/14/06  1            0            2
10028   05/10/07  0            1            2

[Zhongda]

Hi Peter,

Thank you so much for your kind help. This code does work very well, but could you please explain the syntax so that I can have a better understanding for each line.

Really appreciate your help.

Zhongda