Date:         Wed, 9 Feb 2005 21:20:54 -0500
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Subject:      Re: flagging within by group overlapping dates

Michael Murff wrote:
> Hi SAS-L,
>
>
>
> I have a file with by groups defined by id1. Each record is defined
> as a combination of id1,id2 along with a begdat and enddat, which are
> numeric indicators of dates (not SAS dates). I need to be able to
> flag all those records that have overlapping dates with any other
> record WITHIN a by group. Within same by group, if a record's begdate
> is on the same day another record's ends then that would be
> considered overlap. The hard part about this problem is that the
> number of records within a by group is unknown.

Here is a version that does a subselect query to count the number of
overlaps within group id1.

data test;
  do id1=1 to 100;
  do id2=1 to ranuni(1)*5;
    begdat=round(ranuni(1)*5);
    enddat=round(ranuni(1)*20);
    output;
  end;
  end;
run;

proc sql;
  create table test2 as
  select *
       , (select count(*)
          from test as inner
          where inner.id1 eq outer.id1
            and inner.id2 ne outer.id2
            and (inner.begdat between outer.begdat and outer.enddat
                 or
                 inner.enddat between outer.begdat and outer.enddat
                )
         ) as flag
  from test as outer
  ;
quit;


--
Richard A. DeVenezia
http://www.devenezia.com/