Date:         Thu, 25 Jun 2009 11:47:46 +0200
Reply-To:     karma <dorjetarap@GOOGLEMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         karma <dorjetarap@GOOGLEMAIL.COM>
Subject:      Re: SQL - except
Comments: To: Steve Steve <steve11145@yahoo.com>
In-Reply-To:  <842702.17333.qm@web111918.mail.gq1.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1

Hi Steve,

Use except all. The all keyword stops proc SQL from removing duplicates

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001361224.htm


2009/6/25 Steve Steve <steve11145@yahoo.com>:
> 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
> ;
>
>
>
>
>