BookmarkSubscribeRSS Feed
wildhogs
Calcite | Level 5
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
3 REPLIES 3
DBailey
Lapis Lazuli | Level 10
proc sql;
select product, treatment, min(rate) as Min_Rate
from work.data
where date >= (date()-36)
group by product, treatment;
quit;
Ksharp
Super User
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
wildhogs
Calcite | Level 5
Thanks a lot ... I couldn't see the forest for the trees ... LOL

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 668 views
  • 0 likes
  • 3 in conversation