BookmarkSubscribeRSS Feed
EdrickLearnSAS
Calcite | Level 5

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.

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

EdrickLearnSAS
Calcite | Level 5

Thanks RW9.

i'll try it

Ksharp
Super User

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;
ndp
Quartz | Level 8 ndp
Quartz | Level 8

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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