Date: Tue, 11 Oct 2005 13:04:03 -0700
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
Subject: Re: proc sort nodupkey in sql
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
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.
create table x1x as select * from ps.psmpl group by
char(hospid)||lastnm||firstnm; /*doesnt matter which of the
duplicates is retained*/
If you have any comment on that, I would greatly appreciate it.
Richard A. DeVenezia wrote:
> firstname.lastname@example.org wrote:
>>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;
>>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));
> * if you have a distinct row identifier;
> proc sql;
> create table bar as
> select * from foo
> group by key having rowid = min(rowid)
> * 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)