| Date: | Thu, 25 Jun 2009 02:28:34 -0700 |
|---|
| Reply-To: | Steve Steve <steve11145@YAHOO.COM> |
|---|
| Sender: | "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> |
|---|
| From: | Steve Steve <steve11145@YAHOO.COM> |
|---|
| Subject: | SQL - except |
|---|
| Content-Type: | text/plain; charset=iso-8859-1 |
|---|
I want to remove corresponding records in data have1 if they are also in data have2, but one record only. How to do it. Thanks!
data have1;
format date date7.;
input pat lab $ date date7.;
cards;
1 AA 01JUN09
1 AA 01JUN09
1 AA 11JUN09
2 AA 12MAY09
2 AA 13MAY09
2 AA 20MAY09
2 AA 20MAY09
;
run;
data have2;
format date date7.;
input pat lab $ date date7.;
cards;
1 AA 01JUN09
2 AA 20MAY09
;
run;
proc sql;
create table aa as
select pat, lab, date
from have1
except
select pat, lab, date
from have2
order by pat, lab, date
;
data got:
Obs PAT LAB DATE
1 1 AA 11JUN09
2 2 AA 12MAY09
3 2 AA 13MAY09
data want;
1 AA 01JUN09
1 AA 11JUN09
2 AA 12MAY09
2 AA 13MAY09
2 AA 20MAY09
;
|
