Date: Wed, 28 Jan 2004 09:36:24 -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: Question about Merge .. Fuzzy match /SQL?
"Luke" <firstname.lastname@example.org> wrote in message
> I have two files. For simplicity we will call them A and B. Both have
> key matching variables. Among the key variables in each file, we have a
> beginingTime and EndTime. Data layout:
> File A:
> Key1 Key2 BeginingTime Endtime PartNum Key1 Key2 BeginningTime
> Here is the question: I want to the records where all cases match (easy
> I also know that there are cases where key1 and key2 in each file match
> beginingTime in file B as well as the Endtime of B are fully contained in
> beginningTime and Endtime of B. In those cases I want to call it a match
> By fully contained, I have BeginningTimeB <= BeginningA and EndtimeB <=
> Endtime B.
> M final output will be all the data in B and the PartNum in file A.
> Any suggestions?
You murked up the problem statement, but I presume you want the records from
A whose intervals are wholly contained in intervals of B.
SQL is very appropriate
select distinct fileA.*
from fileA, fileB
and fileA.start >= fileB.start
and fileA.end <= fileB.end
Assuming your data is reliable (i.e. start is always <= end in every record)
Richard A. DeVenezia