topic Re: PROC SQL determine min rate in the past 36 days in SAS Procedures

PROC SQL determine min rate in the past 36 days

wildhogs — Tue, 12 Apr 2011 23:45:27 GMT

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

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

DBailey — Wed, 13 Apr 2011 03:07:22 GMT

proc sql;
select product, treatment, min(rate) as Min_Rate
from work.data
where date >= (date()-36)
group by product, treatment;
quit;

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

Ksharp — Wed, 13 Apr 2011 03:42:05 GMT

I do not think sub-query will be more efficient than cartesian product.
So I used Cartesian Product.

[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

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

wildhogs — Wed, 13 Apr 2011 12:15:49 GMT

Thanks a lot ... I couldn't see the forest for the trees ... LOL