PROC SQL: Find Observation Corresponding to Summarized Value

[djbateman]

I am trying to find a way to find the corresponding date to a summary value (like the minimum or maximum).

For example, refer to the data below.  I want to find the date that matches the minimum value.  So, Subject 001 has a minimum value of 179, which occurs on 26MAR2013 (I want to keep the first time the minimum occurs).  Subject 002 has a minimum of 116 on 06JAN2013; Subject 003 has a minimum of 73 on 19AUG2013; and Subject 004 has a minimum of 107 on 24JUL2013.  I am sure that SQL can do this easily, but I can't find a way.  My current method is to use SQL to find the minimum value per patient.  Then I merge the original data with the minimum data on subject and value.  If they match, then I keep the record.  In the case that there is more than one date that matches the minimum (like Subject 001), then I sort the data again and keep the first record.  (See my code below).  Is there a simpler way to do this?

data test;

      input subj $ date value;

      informat date date9.;

      format date date9.;

      cards;

001         01JAN2013   190

001         12FEB2013   184

001         26MAR2013   179

001         07MAY2013   179

002         06JAN2013   116

002         17FEB2013   123

002         31MAR2013   120

003         15APR2013   89

003         27MAY2013   76

003         08JUL2013   75

003         19AUG2013   73

004         20MAR2013   109

004         01MAY2013   112

004         12JUN2013   115

004         24JUL2013   107

004         04SEP2013   109

;

run;

This is my current method:

proc sql;

      create table min as

      select distinct subj, min(value) as value

      from test

      group by subj;

quit;

proc sort data=test;

      by subj value;

run;

data test_min;

      merge test min (in=in_min);

      by subj value;

      if in_min;

run;

proc sort data=test_min;

      by subj date;

run;

data test_min;

      set test_min;

      by subj date;

      if first.subj;

run;

[PGStats · Accepted solution]

You are right, there is a simpler way :

proc sql;

create table dates as

select *

from (

  select *

  from test

  group by subj

  having value=min(value) )

group by subj

having date=min(date);

select * from dates;

quit;

PG