Date:         Tue, 11 Oct 2005 13:04:03 -0700
Reply-To:     toby989@HOTPOP.COM
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby989@HOTPOP.COM
Subject:      Re: proc sort nodupkey in sql
Comments: To: sas-l@uga.edu
In-Reply-To:  <3r2e6tFh7608U1@individual.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Hi Richard

Thanks for your info.

I was trying to do it the most simple way.

My key (and the key that nodupkey in proc sort is using) is the
combination of the 3 variables.

Creating the key on the fly, I was trying following, but SAS sais:
char() requires at least 2 agruments.

proc sql;
create table x1x as select * from ps.psmpl group by
char(hospid)||lastnm||firstnm;     /*doesnt matter which of the
duplicates is retained*/
quit;

If you have any comment on that, I would greatly appreciate it.

Toby




Richard A. DeVenezia wrote:
> toby989@hotpop.com wrote:
>
>>Hi All
>>
>>Is it possible to do the same thing proc sort with nodupkey option is
>>doing in an sql statement (proc sql)?
>>
>>proc sort data=ps.psmpl out=x1 nodupkey;
>>by hospid lastnm firstnm;
>>run;
>>
>>I appreciate your comment.
>
>
> Maybe not exactly -- I don't know the criteria of which row is kept when
> dupkey is encountered.
>
> data foo;
>   do rowid = 1 to 1e5;
>     key = 1 + floor (100*ranuni(1));
>     v1+1;
>     v2+2;
>     output;
>   end;
> run;
>
> * if you have a distinct row identifier;
> proc sql;
>   create table bar as
>   select * from foo
>   group by key having rowid = min(rowid)
>   ;
> quit;
>
> * if you do not have a distinct row identifier;
> * construct one on the fly and dispose when done;
> proc sql;
>   create table bar(drop=sequenceNumber) as
>   select *, monotonic() as sequenceNumber
>     from foo(drop=rowId)
>   group by key having sequenceNumber= min(sequenceNumber)
>   ;
> quit;
>
>