Help using Base SAS procedures

how to count number of events between any two dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

how to count number of events between any two dates

Hello I'm working on some project and need to count number of events between any of two event dates for each firm. I have data look like this:

event datefirm cusipnumber of event +/- 183 daysnumber of events +/-365 daysnumber of event +/- 548 days
1991/2/2121
1992/3/2421
1994/3/7361
1997/6/10361
1998/9/9361
2000/1/11361
2009/6/15361
1996/8/2375
1997/1/23375
1998/3/24375
1999/7/21375
1999/8/26375
1999/11/28375
2000/1/13375
2000/5/8375
2000/6/15375
2000/6/30375
2000/7/4375
2000/7/6375
2000/9/25375
2000/10/5375
2000/10/16375
2001/2/19375
2004/4/1375
2005/6/22375
2005/12/20375
2007/6/14375
2008/4/16375
2008/8/22375
2008/10/23375
2009/3/31375
2009/12/10375
2011/6/12375
2011/8/1375
2012/3/6375
2013/5/7375

For column named number of event +/- , I need to count, for the same firm, number of event dates within the range of [-183,+183], [-365,+365],[-548,+548] relative to the event date on the same row. So for example, for firm with cusip=21, there are 397 days between two event date, 1991/2/21 and 1992/3/24, so in the first row, there are 0 event within the range of [-183,+183] relative to 1991/2/21, but there are 1 event within the range of  [-365,+365], and 1 event within the range of [-548,+548]. In the second row, I need to count the number of event for the same firm within these three ranges relative to the date of 1992/3/24. I need to do the same for three firms. This is the first time I do this kind of counting staff and I tried several method but they do not work on all of the firms. Please help!!

Logan


Accepted Solutions
Solution
‎07-31-2014 01:45 AM
Super User
Super User
Posts: 6,502

Re: how to count number of events between any two dates

data have ;

  length firm event 8;

  informat event yymmdd10.;

  format event yymmdd10.;

  input event firm @@;

cards;

1991/2/21 21 1992/3/24 21

1994/3/7 361 1997/6/10 361 1998/9/9 361 2000/1/11 361 2009/6/15 361

1996/8/2 375 1997/1/23 375 1998/3/24 375 1999/7/21 375 1999/8/26 375

1999/11/28 375 2000/1/13 375 2000/5/8 375 2000/6/15 375 2000/6/30 375

2000/7/4 375 2000/7/6 375 2000/9/25 375 2000/10/5 375 2000/10/16 375

2001/2/19 375 2004/4/1 375 2005/6/22 375 2005/12/20 375 2007/6/14 375

2008/4/16 375 2008/8/22 375 2008/10/23 375 2009/3/31 375

2009/12/10 375 2011/6/12 375 2011/8/1 375 2012/3/6 375 2013/5/7 375

;;;;

proc sql noprint ;

  create table want as

    select a.firm

         , a.event

         , sum( case when abs(a.event - b.event) <= 183 then 1 else 0 end) as n183

         , sum( case when abs(a.event - b.event) <= 365 then 1 else 0 end) as n365

         , sum( case when abs(a.event - b.event) <= 548 then 1 else 0 end) as n548

    from have a left join have b

    on a.firm = b.firm and a.event ne b.event

    group by 1,2

    order by 1,2

  ;

  quit;

proc print width=min;

run;

firm   event    n183 n365 n548

---- ---------- ---- ---- ----

  21 1991-02-21   0    0    1

  21 1992-03-24   0    0    1

361 1994-03-07   0    0    0

361 1997-06-10   0    0    1

361 1998-09-09   0    0    2

361 2000-01-11   0    0    1

361 2009-06-15   0    0    0

375 1996-08-02   1    1    1

375 1997-01-23   1    1    2

375 1998-03-24   0    0    3

375 1999-07-21   3    8   12

375 1999-08-26   3    8   13

375 1999-11-28   4   11   12

375 2000-01-13   8   11   12

375 2000-05-08   9   12   12

375 2000-06-15   8   12   12

375 2000-06-30   8   12   12

375 2000-07-04   8   12   12

375 2000-07-06   8   12   12

375 2000-09-25   8   10   12

375 2000-10-05   8   10   12

375 2000-10-16   8   10   12

375 2001-02-19   3    8   11

375 2004-04-01   0    0    1

375 2005-06-22   1    1    2

375 2005-12-20   1    1    2

375 2007-06-14   0    1    4

375 2008-04-16   1    4    4

375 2008-08-22   2    3    5

375 2008-10-23   2    3    5

375 2009-03-31   1    4    4

375 2009-12-10   0    1    3

375 2011-06-12   1    2    2

375 2011-08-01   1    2    2

375 2012-03-06   0    2    3

375 2013-05-07   0    0    1

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: how to count number of events between any two dates

proc sort data=have;
by firm_cusip event_date;
run;

data want;
set have;
by firm_cusip event_date;
events=event_date-lag(event_date);
if first.firm_cusip then events=0;
if events>=-183 and events<=183 then numer_of_events_183=1;
if events>=-397 and events<=397 then numer_of_events_397=1;
if events>=-548 and events<=548 then numer_of_events_548=1;
run;

Occasional Contributor
Posts: 8

Re: how to count number of events between any two dates

Thanks for your reply,but it seems doesn't work. For example, for the first event date, 21FEB1991, results show that number of events within 183 days is 1, however, it should be 0 because the time interval between this event date and next one is 397, so there is no event between 21FEB1991 and 21FEB1991+183 days. Same for 365 range. Please help!

Solution
‎07-31-2014 01:45 AM
Super User
Super User
Posts: 6,502

Re: how to count number of events between any two dates

data have ;

  length firm event 8;

  informat event yymmdd10.;

  format event yymmdd10.;

  input event firm @@;

cards;

1991/2/21 21 1992/3/24 21

1994/3/7 361 1997/6/10 361 1998/9/9 361 2000/1/11 361 2009/6/15 361

1996/8/2 375 1997/1/23 375 1998/3/24 375 1999/7/21 375 1999/8/26 375

1999/11/28 375 2000/1/13 375 2000/5/8 375 2000/6/15 375 2000/6/30 375

2000/7/4 375 2000/7/6 375 2000/9/25 375 2000/10/5 375 2000/10/16 375

2001/2/19 375 2004/4/1 375 2005/6/22 375 2005/12/20 375 2007/6/14 375

2008/4/16 375 2008/8/22 375 2008/10/23 375 2009/3/31 375

2009/12/10 375 2011/6/12 375 2011/8/1 375 2012/3/6 375 2013/5/7 375

;;;;

proc sql noprint ;

  create table want as

    select a.firm

         , a.event

         , sum( case when abs(a.event - b.event) <= 183 then 1 else 0 end) as n183

         , sum( case when abs(a.event - b.event) <= 365 then 1 else 0 end) as n365

         , sum( case when abs(a.event - b.event) <= 548 then 1 else 0 end) as n548

    from have a left join have b

    on a.firm = b.firm and a.event ne b.event

    group by 1,2

    order by 1,2

  ;

  quit;

proc print width=min;

run;

firm   event    n183 n365 n548

---- ---------- ---- ---- ----

  21 1991-02-21   0    0    1

  21 1992-03-24   0    0    1

361 1994-03-07   0    0    0

361 1997-06-10   0    0    1

361 1998-09-09   0    0    2

361 2000-01-11   0    0    1

361 2009-06-15   0    0    0

375 1996-08-02   1    1    1

375 1997-01-23   1    1    2

375 1998-03-24   0    0    3

375 1999-07-21   3    8   12

375 1999-08-26   3    8   13

375 1999-11-28   4   11   12

375 2000-01-13   8   11   12

375 2000-05-08   9   12   12

375 2000-06-15   8   12   12

375 2000-06-30   8   12   12

375 2000-07-04   8   12   12

375 2000-07-06   8   12   12

375 2000-09-25   8   10   12

375 2000-10-05   8   10   12

375 2000-10-16   8   10   12

375 2001-02-19   3    8   11

375 2004-04-01   0    0    1

375 2005-06-22   1    1    2

375 2005-12-20   1    1    2

375 2007-06-14   0    1    4

375 2008-04-16   1    4    4

375 2008-08-22   2    3    5

375 2008-10-23   2    3    5

375 2009-03-31   1    4    4

375 2009-12-10   0    1    3

375 2011-06-12   1    2    2

375 2011-08-01   1    2    2

375 2012-03-06   0    2    3

375 2013-05-07   0    0    1

Occasional Contributor
Posts: 8

Re: how to count number of events between any two dates

Hello Tom, thanks for your help and it works for most of the deals. However, it does not work so well if there are two or more events happened on the same day.  The program will keep only one of the event if multiple event happened on the same day. If I have an ID variable used to identify different event happened on the same day, can this problem be solved. Thank you!

Super User
Super User
Posts: 6,502

Re: how to count number of events between any two dates

So why don't you just make an ID variable?

data want ; set have; id+1; run;

Super Contributor
Posts: 297

Re: how to count number of events between any two dates

Hi AJ,

I don't understand how you are getting "1 event within the range of  [-365,+365], and 1 event within the range of [-548,+548]" for firm cusip 21?

My results replicate presented in Tom's post.

Regards,

Scott

Occasional Contributor
Posts: 8

Re: how to count number of events between any two dates

You are correct Scott, my mistake

Occasional Contributor
Posts: 8

Re: how to count number of events between any two dates

Hello Scott, the program Tom write works well only if there is one event on the event date.  The program will keep only one of the event if multiple event happened on the same day. If I have an ID variable used to identify different event happened on the same day, can this problem be solved. Thank you!

Super User
Posts: 9,687

Re: how to count number of events between any two dates

data have ;
  length firm event 8;
  informat event yymmdd10.;
  format event yymmdd10.;
  input event firm @@;
cards;
1991/2/21 21 1992/3/24 21
1994/3/7 361 1997/6/10 361 1998/9/9 361 2000/1/11 361 2009/6/15 361
1996/8/2 375 1997/1/23 375 1998/3/24 375 1999/7/21 375 1999/8/26 375
1999/11/28 375 2000/1/13 375 2000/5/8 375 2000/6/15 375 2000/6/30 375
2000/7/4 375 2000/7/6 375 2000/9/25 375 2000/10/5 375 2000/10/16 375
2001/2/19 375 2004/4/1 375 2005/6/22 375 2005/12/20 375 2007/6/14 375
2008/4/16 375 2008/8/22 375 2008/10/23 375 2009/3/31 375
2009/12/10 375 2011/6/12 375 2011/8/1 375 2012/3/6 375 2013/5/7 375
;;;;
proc sql  ;
  create table want as
    select a.firm
         , a.event
           ,(select count(*) from have where event between a.event and a.event+183) as n183
 from have a;
  quit;

Xia Keshan

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 758 views
  • 0 likes
  • 5 in conversation