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 :

 Event Start End Mall1 1-May 5-May Mall1 3-May 7-May Mall1 11-May 14-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
Posts: 9,599

## 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

Thanks RW9.

i'll try it

Super User
Posts: 10,784

## 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
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;

Discussion stats
• 4 replies
• 437 views
• 10 likes
• 4 in conversation