Date: Tue, 20 Oct 2009 22:15:02 -0400 Muthia Kachirayan "SAS(r) Discussion" Muthia Kachirayan Re: how to do this kind of tricky selection? <200910202309.n9KGQnQ3018758@malibu.cc.uga.edu> text/plain; charset=ISO-8859-1

Jerry,

The data step solution is longer than SQL solutions you have received. Now it takes 2 steps. First is done to find the maximum of dx_count within patientID. The new data set and the 2 steps follow.

data patient; input patientID \$ disease_type \$ dx_count dx_date_latest mmddyy8.; datalines; A flu 2 12/27/75 A cancer 3 12/30/76 A copd 3 12/30/76 B adhd 5 01/06/88 C infec 2 05/22/91 C dep 3 04/18/92 C lek 3 04/18/92 C eye 4 02/16/93 D dep 3 05/01/05 D flu 3 05/01/05 E adhd 2 06/12/92 E copd 2 09/12/92 F eye 3 11/18/05 F cancer 4 01/18/02 ; run;

data step; do until(last.patientID); set patient; by patientID; max_count = max(max_count, dx_count); end; do until(last.patientID); set patient; by patientID; output; end; run;

data need; do until(last.patientID); set step; by patientID; if first.patientID then do; prev_disease = disease_type; prev_date = dx_date_latest; end; else do; ind = ( (dx_count = max_count) and (dx_date_latest = prev_date) and (disease_type ne prev_disease) ); if ind then output; prev_disease = disease_type; prev_date = dx_date_latest; end; end; drop prev_date ind max_count; run;

proc print data = need; format dx_date_latest mmddyy8.; run; Kind regards,

Muthia Kachirayan

On Tue, Oct 20, 2009 at 7:09 PM, Jerry <i89rt5@gmail.com> wrote: