Counting number of observations within date range by variable

Reply
Occasional Learner
Posts: 1

Counting number of observations within date range by variable

[ Edited ]

Hi,

I have a data set such as the following, with date format YYMMDD10:

 

ID         Date            Count -5 days
004 01/11/2016 0 007 2011/03/10 0 007 2012/12/04 0 007 2012/12/05 1 007 2012/12/05 2 007 2012/12/07 3 008 2012/10/01 0 008 2012/10/04 1 010 2014/11/25 0 010 2015/04/19 0 010 2015/04/20 1

 

I want to count how many times the ID variable occurs within -5 days, or potentially +/-5 days.

 

Any help would be really appreciated.

 

Thanks

Super User
Posts: 10,239

Re: Counting number of observations within date range by variable

A basic SQL solution looks like this:

data have;
input id date :yymmdd10.;
format date yymmdd10.;
cards;
004        2016/01/11
007        2011/03/10
007        2012/12/04
007        2012/12/05
007        2012/12/05
007        2012/12/07
008        2012/10/01
008        2012/10/04
010        2014/11/25
010        2015/04/19
010        2015/04/20
;
run;

proc sql;
create table want as
select
  a.id,
  a.date,
  (select count(*) from have b where a.id = b.id and 0 <= a.date - b.date <= 5) -1 as count
from have a
;
quit;

but it delivers a different count for the first of the double dates.

Note how I presented your example data in a data step, so that others can easily recreate the dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,542

Re: Counting number of observations within date range by variable

If your requirement is to NOT count the same date you need to explicitly include that in your requirement. Otherwise the same date will count as it is within 5 (or 7 or whatever ) since 0 is in pretty much all -i to +i intervals.

Ask a Question
Discussion stats
  • 2 replies
  • 147 views
  • 0 likes
  • 3 in conversation