selecting Min Rate within a group of records

[wildhogs]

For this example I need to check each row in the following table and determine the minimum rate for each group of records for the past 2 days.

In reality I need to check the past 36 days...

Product Treatment date rate
================================
M H 2011-02-28 2.29 = 2.29
M H 2011-03-01 2.19 = 2.19
M H 2011-03-02 2.09 = 2.09

M M 2011-02-28 3.39 = 3.39
M M 2011-03-01 3.49 = 3.39
M M 2011-03-02 3.59 = 3.39

M L 2011-02-28 4.79 = 4.79
M L 2011-03-01 4.69 = 4.69
M L 2011-03-02 4.89 = 4.69

Thanks

[Ksharp]

Opps.



[pre]











data temp;
input Product $ Treatment $ Date : yymmdd12. Rate ;
format date yymmdd10.;
datalines;
Mortgage High 2011-02-28 2.29
Mortgage High 2011-03-01 2.19
Mortgage High 2011-03-02 2.09
Mortgage Med 2011-02-28 3.29
Mortgage Med 2011-03-01 3.19
Mortgage Med 2011-03-02 3.09
Mortgage Low 2011-02-28 4.29
Mortgage Low 2011-03-01 4.19
Mortgage Low 2011-03-02 4.09
Mortgage Low 2011-03-03 4.99
;
run;
proc sql;
create table min_rate as
select a.*,min(b.rate) as min_rate
from temp as a,temp as b
where a.product = b.product and a.treatment = b.treatment
and b.date between a.date-1 and a.date
group by a.product,a.treatment,a.date,a.rate
;
quit;[/pre]


Ksharp Message was edited by: Ksharp