- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks RW9.
i'll try it
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;