LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (January 2004, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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" <> wrote in message > > I have two files. For simplicity we will call them A and B. Both have some > key matching variables. Among the key variables in each file, we have a > beginingTime and EndTime. Data layout: > > File A: File > B: > Key1 Key2 BeginingTime Endtime PartNum Key1 Key2 BeginningTime > Endtime > > > Here is the question: I want to the records where all cases match (easy one). > I also know that there are cases where key1 and key2 in each file match but the > beginingTime in file B as well as the Endtime of B are fully contained in the > beginningTime and Endtime of B. In those cases I want to call it a match also. > > 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 where fileA.key1=fileB.key1 and fileA.key2=fileB.key2 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

Back to: Top of message | Previous page | Main SAS-L page