Date: Thu, 20 Nov 2003 10:50:27 -0500
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: How To Match/Merge Multiple Observations to Multiple Observat
ions.
Content-Type: text/plain
Pat:
What you are describing as the main problem has a straightforward SQL
solution:
....
select t1.*,t2.printerID
from SMF6 as t1 left join controlDB as t2
on t1.LUNAME=t2.LUNAME
where t1.date between t2.startDate and t2.endDate
;
quit;
The more difficult problem of atypical outcomes will likely arise in joins
of large numbers of rows of datasets. For example, the yield of the query
will multiply rows in SMF6 that match on the ON and WHERE conditions to more
than one row in the control dataset. Also, some rows in SMF6 may not match
any rows in the control dataset and will have missing printerID values. You
can use SAS SQL to test for atypical outcomes and deal with them separately.
Sig
-----Original Message-----
From: Pat Monk [mailto:Pat_Monk@US.CRAWCO.COM]
Sent: Thursday, November 20, 2003 9:52 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: How To Match/Merge Multiple Observations to Multiple Observations.
I have a SAS database containing 4MM+ observations built from SMF type6
records. I have a second "control" database that I need to "match" to the
first database on a variable named LUNAME.
The control database contains observations that identify the remote printer
id (Rnnnn) for a given LUNAME as of a certain date range. Now for the
problem...
The control database may contain multiple observations for a given LUNAME,
based upon a START DATE-END DATE range identified in the control record.
In other words, for a given LUNAME found in an SMF type6 record, the
associated Rnnnn is to be determined by the date within the type6 SMF record
falling in a given date range in a matching control record.
I have not found a technique that will successfully match the SMF records to
the appropriate control record in order to determine the correct Rnnnn for
any given date.
Can you suggest a solution to my multiple-to-multiple match problem?
Pat Monk
Senior Network Systems Programmer
IT Technical Services
Office: 404 843-6203
Fax: 404 705-6817
