## Use proc sql to find min value

Solved
Occasional Contributor
Posts: 15

# Use proc sql to find min value

Hello everyone,

I have a time series data with sale information and want to find non-zero min price in previous 5 minutes.

Input

Time  Price

9:00    10

9:05    11

9:06      0

9:07    13

9:09      0

9:12    15

Desired output

Time  Price  Lag_Min_Price

9:00    10             .

9:05    11           10

9:06      0           11

9:07    13           11

9:09      0           11

9:12    15           13

I try proc sql, but there is something wrong. The problem might be min(price > 0), but I don't know how to correct it. Does anyone have solution? Thank you.

proc sql;
create table want as
select A.*,
(select min(price > 0)
from have as B
where A.time-5<=B.time<=A.time-1) as Lag_Min_Price

from have as A;
quit;

Best,

Tammy

Accepted Solutions
Solution
‎02-21-2014 10:26 AM
Posts: 5,521

## Re: Use proc sql to find min value

Try this:

data have;

input Time :time5.  Price;

format time time5.;

datalines;

9:00    10

9:05    11

9:06      0

9:07    13

9:09      0

9:12    15

;

proc sql;

create table want as

select A.*,

(select min(price)

from have

where price > 0 and time between A.time-300 and A.time) as Lag_Min_Price

from have as A;

select * from want;

quit;

PG

PG

All Replies
Super User
Posts: 5,876

## Re: Use proc sql to find min value

I don't know if this resolve your whole requirement, but I would use the price > 0 in the sub-query where-clause instead.

Data never sleeps
Contributor
Posts: 33

## Re: Use proc sql to find min value

I've done with a datastep and a sort before.

proc sort data=price; by time;run;

data want (keep=price time min_price);

set price;

by time;

_1=time-lag1(time);

_2=time-lag2(time);

_3=time-lag3(time);

_4=time-lag4(time);

_5=time-lag5(time);

p_1=lag1(price);

p_2=lag2(price);

p_3=lag3(price);

p_4=lag4(price);

p_5=lag5(price);

if _1=60 then do;

if p_1 ne 0 then price1=p_1;

end;

if _1=120 then do;

if p_1 ne 0 then price2=p_1;

end;

if _1=180 then do;

if p_1 ne 0 then price3=p_1;

end;

if _1=240 then do;

if p_1 ne 0 then price4=p_1;

end;

if _1=300 then do;

if p_1 ne 0 then price5=p_1;

end;

if _2=120 then do;

if p_2 ne 0 then price2=p_2;

end;

if _2=180 then do;

if p_2 ne 0 then price3=p_2;

end;

if _2=240 then do;

if p_2 ne 0 then price4=p_2;

end;

if _2=300 then do;

if p_2 ne 0 then price5=p_2;

end;

if _3=180 then do;

if p_3 ne 0 then price3=p_3;

end;

if _3=240 then do;

if p_3 ne 0 then price4=p_3;

end;

if _3=300 then do;

if p_3 ne 0 then price5=p_3;

end;

if _4=240 then do;

if p_4 ne 0 then price4=p_4;

end;

if _4=300 then do;

if p_4 ne 0 then price5=p_4;

end;

if _5=300 then do;

if p_5 ne 0 then price5=p_5;

end;

min_price=min(of price1-price5);

run;

Solution
‎02-21-2014 10:26 AM
Posts: 5,521

## Re: Use proc sql to find min value

Try this:

data have;

input Time :time5.  Price;

format time time5.;

datalines;

9:00    10

9:05    11

9:06      0

9:07    13

9:09      0

9:12    15

;

proc sql;

create table want as

select A.*,

(select min(price)

from have

where price > 0 and time between A.time-300 and A.time) as Lag_Min_Price

from have as A;

select * from want;

quit;

PG

PG
Occasional Contributor
Posts: 15

## Re: Use proc sql to find min value

Thank you so much, PG.

🔒 This topic is solved and locked.