## PROC SQL determine min rate in the past 36 days

Occasional Contributor
Posts: 7

# PROC SQL determine min rate in the past 36 days

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
Contributor
Posts: 24

## Re: PROC SQL determine min rate in the past 36 days

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;
Discussion stats
• 1 reply
• 103 views
• 0 likes
• 2 in conversation