topic Re: How to count # of days without overlapping? in SAS Programming

How to count # of days without overlapping?

EdrickLearnSAS — Tue, 04 Aug 2015 09:35:49 GMT

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.

Re: How to count # of days without overlapping?

RW9 — Tue, 04 Aug 2015 09:55:34 GMT

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;

Re: How to count # of days without overlapping?

EdrickLearnSAS — Tue, 04 Aug 2015 10:15:44 GMT

Thanks RW9.

i'll try it

Re: How to count # of days without overlapping?

Ksharp — Tue, 04 Aug 2015 12:25:31 GMT

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;

Re: How to count # of days without overlapping?

ndp — Tue, 04 Aug 2015 16:13:26 GMT

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;