How to count # of days without overlapping?

Reply
New Contributor
Posts: 3

How to count # of days without overlapping?

Hi,

I want to calculate number of event days without overlapping count of days.

Example set of data :

EventStartEnd
Mall11-May5-May
Mall13-May7-May
Mall111-May14-May
:

From this set of data, then for Mall1 count of days is : 1-7may + 11-14may = 7+4 = 11 days

Need help how to sum up days for Mall1 without overlapping.

Currently what i do is sum up (start-end) group by Event(Mall1) and my result is 14

Thanks before.

Super User
Super User
Posts: 7,720

Re: How to count # of days without overlapping?

Well, there's two ways I can think of off the bat.  The first would be to create a "corrected" intermediary dataset where the start is updated to the previous end date where needed:

data have;

  event="Mall1";

  start="01may2015"d; end="05may2015"d; output;

  start="03may2015"d; end="07may2015"d; output;

  start="11may2015"d; end="14may2015"d; output;

  format start end date9.;

run;

data want;

  set have;

  retain edate;

  if start < edate then start=edate;

  edate=end;

  format edate date9.;

run;

The other would just be to create the sequence of dates and nodup sort:

data want;

  set have;

  do i=start to end;

    nd=i;

    output;

  end;

  format nd date9.;

run;

proc sql;

  create table WANT as

  select  distinct EVENT,

          count(1) as CNT

  from    (select distinct EVENT,ND from WANT);

quit;

New Contributor
Posts: 3

Re: How to count # of days without overlapping?

Thanks RW9.

i'll try it

Super User
Posts: 9,874

Re: How to count # of days without overlapping?

Code: Program

data have;
  event="Mall1";
  start="01may2015"d; end="05may2015"d; output;
  start="03may2015"d; end="07may2015"d; output;
  start="11may2015"d; end="14may2015"d; output;
  event="Mall2";
  start="11may2015"d; end="14may2015"d; output;
  format start end date9.;
run;
data want;
if _n_ eq 1 then do;
  declare hash ha();
  ha.definekey('n');
  ha.definedone();
end;
set have;
by event;
if first.event then do;ha.clear();count=0;end;
do n=start to end;
if ha.check() ne 0 then do;ha.add();count+1;end;
end;
if last.event;
drop n start end;
run;
Contributor ndp
Contributor
Posts: 61

Re: How to count # of days without overlapping?

Try this (data needs to be sorted):

data want;

set have;

by event start end;

retain _start days;

if first.event then do; days=end-start+1; _start=end;end;

else if start>_start then do; days=days+(end-start+1); _start=end;end;

else if end>_start then do; days=days+(end-_start); end;

if last.event;

drop _:;

run;

Ask a Question
Discussion stats
  • 4 replies
  • 391 views
  • 10 likes
  • 4 in conversation