topic Counting number of observations within date range by variable in SAS Programming

Counting number of observations within date range by variable

Jerram — Mon, 19 Mar 2018 12:03:39 GMT

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

Re: Counting number of observations within date range by variable

Kurt_Bremser — Mon, 19 Mar 2018 12:52:57 GMT

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.

Re: Counting number of observations within date range by variable

ballardw — Mon, 19 Mar 2018 16:34:58 GMT

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.