Date:         Fri, 11 Jan 2008 14:28:20 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: PROC SQL--select DISTINCT
Comments: To: ben.powell@cla.co.uk
In-Reply-To:  <200801111039.m0ANrfXT002992@mailgw.cc.uga.edu>
Content-Type: text/plain; charset="Windows-1252"

Ben ,

Technically no you can have more than one Distinct, however it does matter the context in which you use it in:

Data Have ;
Infile Cards ;
Input X Y ;
Cards ;
1 1
1 2
1 1
1 2
2 1
2 2
2 3
;
Run ;


Proc SQL ;
Create Table Need As
 Select Distinct X , Count( Distinct Y ) As MyCnt
  From Have
   Group By X ;
Quit ;


Proc Print
 Data = Need ;
Run ;


Toby Dunn

"Don't bail. The best gold is at the bottom of barrels of crap."
Randy Pausch

"Be prepared. Luck is where preparation meets opportunity."
Randy Pausch


> Date: Fri, 11 Jan 2008 05:39:41 -0500
> From: ben.powell@CLA.CO.UK
> Subject: Re: PROC SQL--select DISTINCT
> To: SAS-L@LISTSERV.UGA.EDU
>
> Now the problem has been solved the quick answer to your question is you
> can only use the DISTINCT keyword once in a given proc sql statement.
>
> So you would never write
>
>> select(distinct VAR1), distinct(VAR2), distinct(VAR3)
>
> For exampple:
>
> data x;
> do i=1 to 100000;
> a=round(ranuni(i)*100);
> b=round(ranuni(i)*100);
> c=round(ranuni(i)*100);
> d=round(ranuni(i)*100);
> output;
> end;
> drop i;
> run;
>
> proc sql;
>
> create table dedupe as
> select distinct a, b, c, d
> from x;
>
> create table dupes as
> select a, b, c, d, count(*) as qty
> from x
> group by a, b, c, d
> having qty>1;
>
> quit;
>
>
> HTH.
>
>
>
> On Thu, 10 Jan 2008 12:54:44 -0500, Tom White  wrote:
>
>>Hello SAS-L
>>
>>I have a SAS data set with many fields (variables).
>>
>>Suppose I would like to write sql code like
>>
>>proc sq;
>> create table FOO2 as
>> select(distinct VAR1), distinct(VAR2), distinct(VAR3),
>> VAR4, VAR5
>>from FOO1
>>group by VAR1, VAR2, VAR3, VAR4, VAR5;
>>quit;
>>
>>SAS does not recognize the distinct function. In other words,
>>I cannot write
>>
>> .....
>> select(distinct VAR1), distinct(VAR2), distinct(VAR3),
>> .....
>>quit;
>>
>>Is there any way to select multiple DISTINCT variables like I am
> attempting to do above?
>>
>>Thank you.
>>T
>>
>>--
>>Are we headed for a recession? Read more on the Money Portal
>>Mail.com Money - http://www.mail.com/Money.aspx?cat=money

_________________________________________________________________
Put your friends on the big screen with Windows Vista® + Windows Live™.
http://www.microsoft.com/windows/shop/specialoffers.mspx?ocid=TXT_TAGLM_CPC_MediaCtr_bigscreen_012008