SAS Programming

DATA Step, Macro, Functions and more
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-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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