selecting Min Rate within a group of records

wildhogs — Tue, 12 Apr 2011 18:59:43 GMT

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

Re: selecting Min Rate within a group of records

Ksharp — Wed, 13 Apr 2011 03:51:43 GMT

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

topic selecting Min Rate within a group of records in SAS Programming

selecting Min Rate within a group of records

Re: selecting Min Rate within a group of records