PROC SQL determine min rate in the past 36 days

[wildhogs]

I am trying to build a query to determine the min rate in the past 36 days.

for this example, I will only specify 2 days to make the example small.

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 Min-Rate
====================================

Mortgage High 2011-02-28 2.29 2.29
Mortgage High 2011-03-01 2.19 2.19
Mortgage High 2011-03-02 2.09 2.09

Mortgage Med 2011-02-28 3.29 3.29
Mortgage Med 2011-03-01 3.19 3.19
Mortgage Med 2011-03-02 3.09 3.09

Mortgage Low 2011-02-28 4.29 4.29
Mortgage Low 2011-03-01 4.19 4.19
Mortgage Low 2011-03-02 4.09 4.09
Mortgage Low 2011-03-03 4.99 4.09

For each date record, I need to examine the past
2 days and determine the minimum rate within each
group ( Mortgage High, Mortgage Med, Mortgage Low )

I think I may need to use a subquery, but not sure on
the coding,

Thanks

[buckeye]

The following should give you what you want. The where clause can be used to fit your criteria. Hope this helps.


DATA TMP;
INPUT PRODUCT $ TREATMENT $ DATE $ RATE MINRATE;
DATALINES;
Mortgage High 2011-02-28 2.29 2.29
Mortgage High 2011-03-01 2.19 2.19
Mortgage High 2011-03-02 2.09 2.09
Mortgage Med 2011-02-28 3.29 3.29
Mortgage Med 2011-03-01 3.19 3.19
Mortgage Med 2011-03-02 3.09 3.09
Mortgage Low 2011-02-28 4.29 4.29
Mortgage Low 2011-03-01 4.19 4.19
Mortgage Low 2011-03-02 4.09 4.09
Mortgage Low 2011-03-03 4.99 4.09
;

PROC SQL;
CREATE TABLE TMPTABLE AS
SELECT PRODUCT, TREATMENT, MIN(RATE) AS LOWESTRATE FROM TMP GROUP BY PRODUCT, TREATMENT;
QUIT;