Date:         Tue, 9 Apr 2002 08:50:33 -0700
Reply-To:     Sigurd Wilson Hermansen <hermans1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Wilson Hermansen <hermans1@WESTAT.COM>
Organization: http://groups.google.com/
Subject:      Re: Merge by range
Content-Type: text/plain; charset=ISO-8859-1

SAS SQL allows this construction even though it proves tricky to use:
/* Using input statements created by Ya. */
data d1;
input ID Date1 :mmddyy8. Date2 :mmddyy8. Var1 $;
format date1 date2 mmddyy8.;
cards;
62 01/15/2001 02/01/2001 W
62 03/01/2001 04/01/2001 M
62 04/01/2001 06/01/2001 C
65 02/01/2001 02/28/2001 M
65 03/01/2001 04/04/2001 M
65 04/05/2001 05/15/2001 B
;

data d2;
input ID Date :mmddyy8. Var2;
format date mmddyy8.;
cards;
62 01/20/2001 6
62 02/01/2001 4
62 03/01/2001 1
65 02/01/2001 9
65 04/04/2001 7
65 05/01/2001 8
65 05/15/2001 3
65 05/16/2001 5
;
proc sql;
  select t2.*,t1.date1,t1.date2,t1.var1
  from d1 as t1 right join d2 as t2
  on t1.ID=t2.ID
  where t2.date between t1.date1 and t1.date2
  group by t2.ID,t1.date1,t1.date2
  having t2.date=max(t2.date)
  ;
quit;

For example, duplicate intervals for the same ID will produce
duplicates in the yield of the query.
Sig
sw75@boxfrog.com (Sheila Whitelaw) wrote in message news:<e97a404.0204080938.4dfebc38@posting.google.com>...
> Thank you very much for all the help. I have never used SQL before in
> SAS, it is very helpful. I have one more question. Consider the
> following example,
>
> Input dataset 1:
>  OBS       ID              Date1               Date2              Var1
>  1         65              03/01/2001      04/04/2001              M
>  2         65              04/01/2001      04/15/2001              B
>
> Input dataset 2:
>  OBS       ID              Date3           Var2
>  1         65              03/04/2001      7
>  2         65              04/01/2001      8
>
> Result:
>  OBS   ID         Var1      Var2
>  1     65          M         7
>  2     65          B         8
>
> In dataset 2, the second record has a date that within the date range
> in both record 1 and record 2 of dataset 1. I need to match it to the
> record that has the latest date (in this case, record 2). Does anyone
> how to do it?
>
> Thanks again.