Hi Wendy,

Sorry, in last post I didn't include your key. This version below will do better once you have more sample data.

data DATA1; ID=1; START='01jan2001'd; END='15jan01'd; output; ID=1; START='16jan2001'd; END='20jan01'd; output; ID=1; START='01feb2001'd; END='05feb01'd; output; ID=1; START='23mar2001'd; END='25mar01'd; output; format start mmddyy10.; format end mmddyy10.; run;

data DATA2; ID=1; PROC='a'; PROCDATE='05jan2001'd; output; ID=1; PROC='b'; PROCDATE='13jan2001'd; output; ID=1; PROC='c'; PROCDATE='25jan2001'd; output; ID=1; PROC='d'; PROCDATE='03feb2001'd; output; ID=1; PROC='e'; PROCDATE='17feb/2001'd; output; ID=1; PROC='f'; PROCDATE='01mar/2001'd; output; ID=1; PROC='g'; PROCDATE='24mar/2001'd; output; format procdate mmddyy10.; run;

proc sql; create table results as select * from data1 as a left join data2 as b on a.id eq b.id and b.procdate between a.start and a.end order by a.id, a.start, b.procdate ; quit;

HTH, Mark

-----Original Message----- From: wendy.watson@COVANCE.COM [mailto:wendy.watson@COVANCE.COM] Sent: Thursday, May 17, 2001 11:19 AM To: SAS-L@LISTSERV.UGA.EDU Subject: matched crossing

Hi SAS Users,

I have a situation where I have 2 large data files. Both files contain one or more observations. What I need to do compare each observation in the first dataset to each observation in the second dataset. I think this is called matched crossing. I would like SQL code, any ideas? Thanks.

For example the 2 files (the BY variable for the merge is ID):

DATA #1 DATA #2

ID START END ID PROC PROCDATE 1 01/01/2001 01/15/01 1 a 01/05/2001 1 01/16/2001 01/20/01 1 b 01/13/2001 1 02/01/2001 02/05/01 1 c 01/25/2001 1 03/23/2001 03/25/01 1 d 02/03/2001 2 ... 1 e 02/17/2001 1 f 03/01/2001 1 g 03/24/2001 2 ...

I would like the final data set to look like the following:

ID START END PROC PROCDATE 1 01/01/2001 01/15/01 a 01/05/2001 b 01/13/2001

1 01/16/2001 01/20/01 . .

1 02/01/2001 02/05/01 d 02/03/2001

1 03/23/2001 03/25/01 g 03/24/2001

----------------------------------------------------- Confidentiality Notice: This e-mail transmission may contain confidential or legally privileged information that is intended only for the individual or entity named in the e-mail address. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or reliance upon the contents of this e-mail is strictly prohibited.

If you have received this e-mail transmission in error, please reply to the sender, so that we can arrange for proper delivery, and then please delete the message from your inbox. Thank you.