PROC SQL: Find Observation Corresponding to Summarized Value

djbateman — Thu, 08 May 2014 15:30:17 GMT

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;

Re: PROC SQL: Find Observation Corresponding to Summarized Value

PGStats — Thu, 08 May 2014 15:41:59 GMT

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;

topic Re: PROC SQL: Find Observation Corresponding to Summarized Value in ODS and Base Reporting

PROC SQL: Find Observation Corresponding to Summarized Value

Re: PROC SQL: Find Observation Corresponding to Summarized Value