Date:         Mon, 2 Nov 1998 11:15:39 -0500
Reply-To:     "Gary M. McQuown" <mcquown@FENIX2.DOL-ESA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         "Gary M. McQuown" <mcquown@FENIX2.DOL-ESA.GOV>
Subject:      SQL, Mutually Exclusive Codes by ID???
Content-Type: text/plain; charset="iso-8859-1"

Hello, I have another problem that may be best resolved with SQL.
 
There are two very large data sets, Billed (10 million obs) and Exclude
(60,000 obs).  Billed contains variables ID and CPT and shows what
procedures each patient was billed for.  The number of observations per ID
ranges from 1 to several hundred.  Exclude contains variables ME_1 and ME_2,
pairs of CPT codes that can not be billed together under certain conditions.
 
I need to identify any occurrence where, by ID, CPT=ME_1 and ME_1=ME_2
(where ME_1 and ME_2 are paired in Exclude) and where for ANY other
observation for that ID, CPT=ME_2.
 
 
 Thanks,
 
 Gary
 
 
 File Billed
 
 ID   CPT
 1    01
 1    99
 1    88
 2    02
 2    99
 2    98
 
 File Exclude
 
 me_1  me_2
 
 01    99
 01    98
 02    98
 02    97
 
 
 Output
 ID   CPT   ME_2
 01   01    99
 02   02    98