BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Zhongda
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Super User

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

View solution in original post

2 REPLIES 2
PeterClemmensen
Super User

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
Fluorite | Level 6

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 2 replies
  • 288 views
  • 1 like
  • 2 in conversation