Date:         Mon, 25 Sep 2006 10:23:55 -0700
Reply-To:     "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark" <Mterjeson@RUSSELL.COM>
Subject:      Re: SQL "nodupkey" or "first.key" equivalent
Comments: To: mcquown@DASCONSULTANTS.COM
Content-Type: text/plain; charset="us-ascii"

Hi Gary,

 

Since you mention first.  and most-recent (which many times is last.),

I will provide two different methods for both first. and last.

 

 

    * make sample data ;

data sample;

    name='Barney'; num=1; output;

    name='Barney'; num=2; output;

    name='Barney'; num=3; output;

    name='Fred  '; num=1; output;

    name='Fred  '; num=2; output;

    name='Betty '; num=1; output;

    name='Betty '; num=2; output;

    name='Betty '; num=3; output;

    name='Wilma '; num=1; output;

run;

 

 

    * FIRST. ;

    * SQL with join ;

proc sql ;

    create table result1 as

    select

            a.*

    from

            sample as a

        inner join

            (select name, min(num) as first_num

             from sample

             group by name) as b

        on

             a.name eq b.name

        and

             a.num eq b.first_num

    ;

quit;

 

 

    * FIRST. ;

    * SQL with monotonic() ;

proc sql ;

    create table result2(drop=N) as

    select

             *,

             monotonic() as N

    from

             sample

    group by

             name

    having

             min(N) eq N

    ;

quit;

 

 

 

 

 

    * LAST. ;

    * SQL with join ;

proc sql ;

    create table result3 as

    select

            a.*

    from

            sample as a

        inner join

            (select name, max(num) as first_num

             from sample

             group by name) as b

        on

             a.name eq b.name

        and

             a.num eq b.first_num

    ;

quit;

 

 

    * LAST. ;

    * SQL with monotonic() ;

proc sql ;

    create table result4(drop=N) as

    select

             *,

             monotonic() as N

    from

             sample

    group by

             name

    having

             max(N) eq N

    ;

quit;

 

 

 

Hope this is helpful,

 

Mark Terjeson

 

 

________________________________

From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Gary McQuown
Sent: Monday, September 25, 2006 8:01 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: SQL "nodupkey" or "first.key" equivalent

 

All,

Is there a SQL equivalent to proc sort nodupkey or the data step "if
first.key then output"?

 

The goal is to keep the most recent obs that results from a join without
having to read the data again. 


thanks,
Gary