Date:         Fri, 27 Mar 2009 09:58:35 -0500
Reply-To:     "./ ADD NAME=Data _null_;" <iebupdte@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "./ ADD NAME=Data _null_;" <iebupdte@GMAIL.COM>
Subject:      Re: Determining Overlapping Date Spans
Comments: To: Jack Clark <jclark@hilltop.umbc.edu>
In-Reply-To:  <456B52C41B724C41B96561D7AD283E7DEFEB7B@mail.chpdm.umbc.edu>
Content-Type: text/plain; charset=ISO-8859-1

If you expand the ranges then you don't need to write all those if
statements.  HOWEVER if you have too much data this will be too
inefficient.  It could be used as a check.

W is W span number, array index.
R is R span number, index created below.
MIN and MAX are overlaped dates.

data waiver_spans;
  infile cards missover;
  input @01 recipno  $3.
        @05 wvbegdt1 mmddyy8.
            @14 wvenddt1 mmddyy8.
            @23 wvbegdt2 mmddyy8.
            @32 wvenddt2 mmddyy8.
            @41 wvbegdt3 mmddyy8.
            @50 wvenddt3 mmddyy8.
            ;
   cards;
111 02012008 06302008 08012008 12312008
222 01012008 08302008
333 03012008 03312008 05012008 06302008 11012008 12312008
;
run;
data wspans / view=wspans;
   set waiver_spans;
   array b[*] wvbegdt:;
   array e[*] wvenddt:;
   do w = 1 to dim(b);
      if n(b[w],e[w]) eq 2 then do date = b[w] to e[w];
         output;
         end;
      end;
   format date yymmdd.;
   keep r: w date;
   run;
data rem_spans;
  infile cards missover;
  input @01 recipno $3.
        @05 rembegdt mmddyy8.
            @14 remenddt mmddyy8.
            ;
   cards;
111 07012008 11302008
222 02012008 08012008
222 12012008 12312008
333 01012008 04012008
;
   run;
data rspans / view=rspans;
   set rem_spans;
   by recipno;
   if first.recipno then r = 0;
   r + 1;
   do date = rembegdt to remenddt;
      output;
      end;
   keep rec: r date;
   format date yymmdd.;
   run;
data overlapV / view=overlapV;
   merge wspans(in=in1) rspans(in=in2);
   by recipno date;
   if in1 and in2;
   run;
proc summary nway missing data=overlapV;
   class recipno w r;
   var date;
   output
      out=work.overlap(drop=_type_ rename=(_freq_=days))
      max=beg min=end;
   format days:;
   run;
proc print;
   run;



On 3/27/09, Jack Clark <jclark@hilltop.umbc.edu> wrote:
> Hello,
>
>
>
> I have a question about the best way to determine whether two date
> ranges overlap.  I have two data sets.  The first, WAIVER_SPANS, is one
> record per person (RECIPNO) with an array of beginning and end dates for
> participation in a WAIVER program.  The second, REM_SPANS, could have
> multiple records per person and has a beginning date and end date the
> person was in the REM program.  I have created some sample data below.
>
>
>
> I need to merge the two data sets and flag records where the REM span
> has overlap with any of the WAIVER spans.  I started the code below,
> using a DATA Step merge and arrays.  I need some suggestions on the best
> way to check for span overlap - meaning that there is at least one day
> when the person was in the WAIVER and REM programs on the same day.
> Thanks.
>
>
>
>
>
>
>
> data waiver_spans;
>
>   infile cards missover;
>
>   input @01 recipno  $3.
>
>         @05 wvbegdt1 mmddyy8.
>
>             @14 wvenddt1 mmddyy8.
>
>             @23 wvbegdt2 mmddyy8.
>
>             @32 wvenddt2 mmddyy8.
>
>             @41 wvbegdt3 mmddyy8.
>
>             @50 wvenddt3 mmddyy8.
>
>             ;
>
> cards;
>
> 111 02012008 06302008 08012008 12312008
>
> 222 01012008 08302008
>
> 333 03012008 03312008 05012008 06302008 11012008 12312008
>
> ;
>
> run;
>
>
>
>
>
> data rem_spans;
>
>   infile cards missover;
>
>   input @01 recipno $3.
>
>         @05 rembegdt mmddyy8.
>
>             @14 remenddt mmddyy8.
>
>             ;
>
> cards;
>
> 111 07012008 11302008
>
> 222 02012008 08012008
>
> 222 12012008 12312008
>
> 333 01012008 04012008
>
> ;
>
> run;
>
>
>
>
>
> data waiver_rem;
>
>   merge waiver_spans (in=a) rem_spans (in=b);
>
>   by recipno;
>
> if a and b;
>
> array wvbeg (3) wvbegdt1-wvbegdt3;
>
> array wvend (3) wvenddt1-wvenddt3;
>
> do i = to to 3 until (remflag);
>
>   * check rem span dates through array of waiver spans ;
>
>   * flag records when there is span overlap ;
>
>   if /* ??? */ then remflag = 1;
>
> end;
>
> run;
>
>
>
>
>
>
>
> Jack
>
>
>
>
>
>
>
> Jack Clark
> Senior Research Analyst
> phone: 410-455-6256
> fax: 410-455-6850
> jclark@hilltop.umbc.edu
>
> University of Maryland, Baltimore County
> Sondheim Hall, 3rd Floor
> 1000 Hilltop Circle
> Baltimore, MD 21250
>
>
>
>
> Confidentiality Notice: This e-mail may contain information that is legally privileged and that is intended only for the use of the addressee(s) named above. If you are not the intended recipient, you are hereby notified that any disclosure, copying of this e-mail, distribution, or action taken in reliance on the contents of this e-mail and/or documents attributed to this e-mail is strictly prohibited. If you have received this information in error, please notify the sender immediately by phone and delete this entire e-mail. Thank you.
>