Date:         Wed, 27 Sep 2006 22:29:00 -0400
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject:      Re: Selecting records

On Wed, 27 Sep 2006 10:25:48 -0700, Mogens A. Krogh <MKROGH@DSR.KVL.DK> wrote:

>Well, I think Muthia Kachirayan could have a point about IDs and PROD
>being characters and that the IDs could just be 'clustered' instead of
>sorted. This solution can take care of that and mth doesn't even have
>to be sorted. One point in this example is to avoid that lines with
>missing prod are ever read into the program, with the where=.
>
>I'm quite sure there must be a SQL solution to this too

Sure, like:

   select *
    from a
    where prod is not null
    group by id
    having mth = max(mth)
   ;

>
>Regards
>Mogens A. Krogh
>DVM, PhD-student
>www.kvl.dk
>
>data a;
>length id $ 9;
>infile datalines dsd;
>input id prod $ mth;
>datalines;
>123456789,12345,1
>123456789,12345,2
>123456789,12345,8
>123456789,12879,4
>123456789,12879,5
>123456789,,6
>123467783,16289,1
>123467783,16289,8
>123467783,16289,3
>123467783,,4
>123467783,16772,5
>123467783,16772,6
>123467783,,7
>;
>run;
>data want (rename=(mth1=mth prod=prod1 id1=id));
>retain prod1 mth1 Id1;
>set a (where=(prod^=' ')) end=eof;
>if lag(id)=ID & mth>mth1 then do;
>        prod1=prod;
>        mth1=mth;
>end;
>if lag(id)^=ID & lag(ID)^=' ' then output;
>if lag(ID)^=ID then do;
>        prod1=prod;
>        mth1=mth;
>        id1=id;
>end;
>if eof then output;
>drop mth prod id;
>run;
>
>

[snip]