|
Hi Wendy,
How's this do:
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
left join
data2
on
procdate between start and end
order by
id,
start,
procdate
;
quit;
Hope this is helpful,
Mark Terjeson
Washington State Department of Social and Health Services
Division of Research and Data Analysis (RDA)
terjemw@dshs.wa.gov
-----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.
| 
