Date:         Thu, 9 Dec 2010 00:26:03 -0500
Reply-To:     Muthia Kachirayan <muthia.kachirayan@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Muthia Kachirayan <muthia.kachirayan@GMAIL.COM>
Subject:      Re: request for help with selecting and retaining max values in a
              dataset
In-Reply-To:  <1EA8D326E89D1F478700900CEDC9384A01F5298BE5@ausy-pmsg-mbs01.ap.corp.amgen.com>
Content-Type: text/plain; charset=ISO-8859-1

Hi,

Assuming that the data set, HAVE, is presorted by PATID, double DoW-loop is
the nice way to deal with your need, from among other ways. I have added
some records for patid = 5.

data have;
input patid code count;
cards;
1 0 1
2 0 1
3 0 15
3 1035 8
4 0 6
5 0 1
5 0 12
5 0 3
6 0 9
7 1233 11
8 0 8
8 1797 11
;
run;



data need;
do until(last.patid);
   set have;
   by patid;
   max_count = max(max_count, count);
end;
do until (last.patid);
   set have;
   by patid;
   if max_count = count then output;
end;
drop max_count;
run;

proc print data = need;
run;

Regards,

Muthia Kachirayan

On Wed, Dec 8, 2010 at 11:41 PM, Quayle, Julie-Ann <jquayle@amgen.com>wrote:

> Hi All
> I wondered if there would be any one who could help out there...
>
> Below is an extract of my dataset containing the variables PATID, CODE and
> COUNT.  I need to create a dataset containing one row for each patient, by
> retaining the row that contains the maximum value of the variable COUNT and
> the associated value of the variable CODE for each patient - in the extract
> below, the three rows marked with an asterisk should be dropped.
>
> Any help appreciated...Many thanks, Julie-Ann
>
> Obs     patid    code    COUNT
>
>  1         1    0          1
>  2         2    0          1
>  3         3    0         15
>  4         3    1035       8 *
>  5         4    0          6
>  6         5    0          1
>  7         6    0          9
>  8         7    1233      11
>  9         8    0          8 *
> 10         8    1797      11
> .
> .
> .
> .
> 11473   8988    0          1
> 11474   8989    0          8
> 11475   8990    1035       3
> 11476   8991    1035       1
> 11477   8992    0          1
> 11478   8993    0          6 *
> 11479   8993    1035      10
>