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;
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.