Date:         Fri, 7 Oct 2005 14:04:38 -0700
Reply-To:     "Pardee, Roy" <pardee.r@GHC.ORG>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Pardee, Roy" <pardee.r@GHC.ORG>
Subject:      Re: Proc sql and distinct option
Comments: To: steve.silver@SUNTRUST.COM
Content-Type: text/plain; charset="us-ascii"

Won't that have the same effect as a simple SELECT DISTINCT <<field
list>>?  Which I don't think is what the OP wants--that will consider
values in all the fields & I think she just wants two of the fields
considered.

If it was me, I'd just do a PROC SORT NODUPKEY on the two fields of
interest...

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Steve Silver
Sent: Friday, October 07, 2005 1:52 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Proc sql and distinct option


Nicole,

To remove deduplicate rows using SQL, UNION the same query to itself as
follows:
proc sql;
create table dog as
  select  chm.patid, chm.visit_date,
> rgm.calc_start_date, rgm.calc_end_date
>   from  istrain.dog_hx(where=(oh_drug_typ_id=1)) chm,
>   istrain.regimens_ac rgm
>    where chm.oh_pat_id=rgm.oh_pat_id
union
   select chm.patid,  chm.visit_date,
> rgm.calc_start_date, rgm.calc_end_date
>   from  istrain.dog_hx(where=(oh_drug_typ_id=1)) chm,
>   istrain.regimens_ac rgm
>    where chm.oh_pat_id=rgm.oh_pat_id
;
quit;


Good Luck,

Steve


Nicole Bibb wrote:
> Can u have the distinct option on two variables in proc sql? I can not

> find any material referencing how to dedup on two variables in proc
> sql.
>
> Here is my code but I get an error msg
>
> proc sql;
>  create table dog as
>    select distinct chm.patid, distinct chm.visit_date,
> rgm.calc_start_date, rgm.calc_end_date
>   from  istrain.dog_hx(where=(oh_drug_typ_id=1)) chm,
>   istrain.regimens_ac rgm
>    where chm.oh_pat_id=rgm.oh_pat_id
> quit;