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
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
I don't know if this resolve your whole requirement, but I would use the price > 0 in the sub-query where-clause instead.
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;
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
Thank you so much, PG.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.